Power BI Tips

https://exceleratorbi.com.au/getting-started-dax-studio/  

https://www.youtube.com/watch?v=-x2dLTtKiR8  

https://exceleratorbi.com.au/power-bi-analyze-excel-what-you-need-to-know/  

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-administering-power-bi-in-your-organization https://docs.microsoft.com/en-us/power-bi/admin/service-admin-role Also covers assign users to an admin role in Microsoft 365 Admin Center https://docs.microsoft.com/en-us/power-bi/admin/service-admin-portal

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-distribute-apps Publish an App: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-distribute-apps For business users: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-apps  

https://www.youtube.com/watch?v=8n6tL9PtqBw  

https://www.youtube.com/watch?v=lDjKS3Ur3ks  

https://www.youtube.com/watch?v=XsyeImIK-7g  

  https://www.youtube.com/watch?v=jyxZxFT_pAY

https://www.youtube.com/watch?v=BAOljlkE1UI   SQLBI has a Power BI external tool available that allows you to connect Excel to a model in an open Power BI Desktop.

  https://www.youtube.com/watch?v=m-qhcDcNKrE https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/ Use ALLEXCEPT instead of a measure reference when using a measure in a calculated column: --  Total Sales is a calculated column of Product -- --  Do NOT do this -- Total Sales  =   [Sales Amount]   -- --  Instead, do this: -- Total Sales  =      CALCULATE   (          [Sales Amount] ,          ALLEXCEPT   (   'Product' ,   'Product'[ProductKey]   )      ) Another solution may be replacing instances of: ALL with ALLNOBLANKROW VALUES with DISTINCT -- --  The filter argument in CALCULATE can be expressed using the compact form. --  You write this: -- CALCULATE   (      [Sales Amount] ,      'Product'[Color]   =   "Red" ) ...

https://www.youtube.com/watch?v=JYywNHCKhMk  

  https://powerbi.microsoft.com/en-us/power-bi-pro/

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-per-user-faq Datasets in the  Large Dataset format  in Premium Per User (PPU) will not appear in the user interface, which is by design.

To see the size of datasets: Click Settings (gear icon) Click Manage Group Settings You will see the list of datasets and it includes a Size column

  https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-roles-new-workspaces#licenses When the new workspace is in a Power BI Premium capacity, users with the Viewer role can access the workspace even if they don't have a Power BI Pro or Premium Per User (PPU) license.  However, if you assign these users a higher role like Admin, Member, or Contributor, they're prompted to start a Pro trial when they try to access the workspace.

  https://www.sqlbi.com/tv/explaining-unexpected-results-with-dax-time-intelligence-functions-unplugged-n/

  https://www.youtube.com/watch?v=L-IfcHk4w3I

  https://www.youtube.com/watch?v=6h9Gmw96xNI

  https://www.youtube.com/watch?v=nVvlEHKr_0o

  https://www.youtube.com/watch?v=N6zgLSeHpXk

  https://www.youtube.com/watch?v=nVvlEHKr_0o https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

  https://www.youtube.com/watch?v=tgL7D3JTa_E

  https://www.youtube.com/watch?v=2HkBbqxBzF0&t=5s

  https://www.youtube.com/watch?v=AvdIl5KnTz4

  https://www.youtube.com/watch?v=4-WSXQ6qLec

  https://www.youtube.com/watch?v=Nr-_difQ7vw

  https://www.youtube.com/watch?v=LKj4GLJA-lw

  https://www.youtube.com/watch?v=Tk-7gBt9CDE

  https://www.youtube.com/watch?v=cgIi3iFKOvE

  https://www.youtube.com/watch?v=VPeo5PF4UTM

  https://www.youtube.com/watch?v=EawkPc_iLs4&t=20s

  https://www.youtube.com/watch?v=9qKe66uBce8 Specify a column name for REMOVEFILTERS instead of a table.

  https://www.youtube.com/watch?v=UehWR5wWWEo

  https://www.youtube.com/watch?v=miiGlDWeamM Scenario is to use a ranking measure as a filter on a matrix visual. The filter is used to only return the top 2 based on rank. When the measure is included in the matrix, the results are correct. Problem is when the measure is not included in the matrix, the results are not correct. Solution adds zero to a measure to keep it from returning blank.

https://www.youtube.com/watch?v=kQjYG6TJVp8  

https://www.daxpatterns.com/static-segmentation/ Note on circular dependency - use DISTINCT instead of VALUES.

https://www.sqlbi.com/articles/deep-dive-into-measure-filters-in-power-bi-desktop/  

Create an empty table to be used for Report Measures: _Report Measures =FILTER ( {1}, FALSE )  Create a disconnected table to be used in a slicer to select the measure you want to view: MeasureToView = DATATABLE( "MeasureName", STRING, "Ordinal", INTEGER, { { "Units", 1 }, { "Sales", 2 }, { "Avg Sales", 3 }, { "Trans", 4 }, { "Avg Trans", 5 }, { "Avg Check", 6 } } ) Get the measure based on the selection in the slicer: _Default Measure Selection = VAR MeasureName = SELECTEDVALUE('MeasureToView'[MeasureName]) VAR Result = SWITCH( MeasureName, "Units", [Reporting Units], "Sales", [Sales], "Trans", [Transactions], "Avg Sales", [Average Sales], "Avg Trans", [Average Transactions], "Avg Check", [Average Check] ) RETURN Result

Create a table to use as a filter: Indicators = DATATABLE( "IndicatorName", STRING, "IndicatorValue", INTEGER, { { "IsSystemTotal", 1 } }) Create a measure to check if a visual is being filtered: _Is System Total = ISFILTERED('Indicators'[IndicatorName]) This can be used as an "indicator". Add the following filter to the visual: Indicators[IndicatorName] is IsSystemTotal This will cause the _Is System Total measure to return TRUE.

We had a scenario where the user want to see average on the DETAIL rows and the SUM of the averages on the TOTAL row in a matrix. The  'Calendar'[Full Date] column was on the rows and it was the lowest level of the hierarchy which include week, month, etc. The  'Calendar'[Full Date] column represents a single day. HASONEVALUE will return TRUE for a single day and FALSE otherwise. When FALSE, use SUMX to iterate over the days and calculate the SUM of the averages. Here is the measure: 'Financial Summary'[Average Sales (With Rollup)] =  VAR AverageSales =       AVERAGEX ( 'Financial Summary' , 'Financial Summary'[TransactionAmount] ) RETURN      IF (          HASONEVALUE ( 'Calendar'[Full Date] ) ,          AverageSales,           SUMX ( VALUES ( 'Calendar'[Full Date] ) , AverageSales...

We had a scenario where the user want to see average at the ROW level and the SUM of the averages at the TOTAL level in a matrix. There was no hierarchy on the rows. The rows have the  'Financial Summary Line Type'[Digital Description] column. Here's the brute force solution: LineType gets the value of  the  'Financial Summary Line Type'[Digital Description] column for the DETAIL rows and BLANK() for the TOTAL row. Average Sales (Digital Rollup) = VAR LineType = SELECTEDVALUE('Financial Summary Line Type'[Digital Description], BLANK()) VAR AllDigitalLineTypes = ADDCOLUMNS( CALCULATETABLE( VALUES('Financial Summary Line Type'[Digital Description]), 'Financial Summary Line Type'[Digital] = 1 ), "@AverageSales", CALCULATE(DIVIDE(SUM('Financial Summary'[TransactionAmount]), COUNTROWS('Calendar'))) ) RETURN IF ( NOT ISB...

  https://www.sqlbi.com/articles/using-concatenatex-in-measures/

  https://www.youtube.com/watch?v=nVvlEHKr_0o

  https://www.youtube.com/watch?v=eoEbnQyianU

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/  

TableName = FILTER ( { 1 }, FALSE ) This can be useful to create a table for measures.

  https://www.youtube.com/watch?v=oiCVBUQenls

https://hatfullofdata.blog/power-bi-introducing-sync-slicers/  

https://www.sqlbi.com/articles/variables-in-dax/  

https://www.sqlbi.com/blog/marco/2011/03/08/difference-between-distinct-and-values-in-dax/   Example: If a  FACT table contains values that do not exist in a related DIMENSION table, a BLANK row is automatically added to the DIMENSION table. This DIMENSION row with the BLANK value will join to any FACT row where the column value does not exist in the DIMENSION. DISTINCT will NOT include the BLANK column in the DIMENSION. VALUES will include the BLANK column in the DIMENSION.

 Rename the Calculation Item column name from Name to something else; e.g. same as the name of the Calculation Group is probably okay. When you have a filter on the Calculation Group, you'll see Name if you don't do this. Better to see something more description; e.g. PriceToUse.