Power BI Tips

https://www.daxpatterns.com/week-related-calculations/  

  https://www.youtube.com/watch?v=0QvovI3aycs

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

Video:  https://www.youtube.com/watch?v=xS1t25_GsWw Article and Video:  https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expressions/ " Calculation groups replace measure references with the DAX expression of the calculation item that is active in the filter context." Shows the "pattern" for injecting any parameter from the report inside a measure as the following steps: Rename calculation group as "Internal..." and hide it Create disconnected table which is a copy of the Internal calculation group Use  disconnected table in slicer to allow user to choose the calculation desired Create "Internal" measure Use CALCULATE to apply calculation item based on user selection Copy disconnected table: PriceToUse  = SELECTCOLUMNS   (      InternalPriceToUse ,      "PriceToUse" ,   InternalPriceToUse[InternalPriceToUse] ,      "Ordinal" ,   InternalPriceToUse[...

  https://tabulareditor.com/

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

  https://dax.tips/

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

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

https://www.youtube.com/watch?v=6tEsu7aZ5Tg&t=695s  

  https://exceleratorbi.com.au/filters-on-power-bi-slicers-wait-what/

  https://radacad.com/scenarios-of-using-calculated-tables-in-power-bi Customer Sales = SUMMARIZE(FactInternetSales,FactInternetSales[CustomerKey], 'Total Sales', SUM(FactInternetSales[Total Sales])) Top 10 Customers = TOPN(100,'Customer Sales','Customer Sales'[Total Sales],DESC)

https://radacad.com/filtering-slicer-resolved-in-power-bi  

https://www.mssqltips.com/sqlservertip/6671/dynamic-power-bi-slicer-using-dax-logic/  

https://hatfullofdata.blog/power-bi-resetting-slicers-with-a-bookmark-button/  

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers  

  https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-bookmarks

  https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-filter

https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/   NOTE: "Built-in" filters and slicers affecting this visual (click Filter icon i visual header) is available  when you check the option “Enable the updated filter pane, and show filters in the visual header for this report” in the Report settings of the current file.

This article describes how to compute visual totals accurately, in case a measure does not provide the right result at the total level but works correctly in other rows of the report. https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/   Example: Calculate the number of days where the Sales Amount is > $30,000 with proper rollup to month and year High Days Correct := SUMX (      'Date' ,      IF ( [Sales Amount] > 30000 , 1 ) )

https://www.sqlshack.com/implementing-star-schemas-in-power-bi-desktop/  

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema  

  https://www.youtube.com/watch?v=5TaZQFc-t58

  https://www.youtube.com/watch?v=xy9nmSQeUWg&t=10s

  Set categorization on columns; e.g. web image, web URL, geography, etc. Disable summarizing numeric columns that you do not summarize; e.g. key columns Hide columns that are not used in reports; e.g. key columns Disable Auto Date/Time

  RELATIONSHIPS: 'Unit'.[UnitNumber] (1) --> 'Delivery Order Cash Recon'.Unit (many) MEASURES: [Active Unit Count] =  VAR Units = DISTINCTCOUNT(     'Delivery Order Cash Recon'[Unit] ) RETURN  IF(     ISBLANK(Units),     0,     Units ) 3, "Active Units" --------------------------------------- [Delivery Sales] =  SUM('Delivery Order Cash Recon'[Sales]) --------------------------------------- [Average Unit Sales] =  DIVIDE ([Delivery Sales], [Active Unit Count]) 4, 'Average Sales"   --------------------------------------- [Transaction Count] = COUNTROWS ('Delivery Order Cash Recon') --------------------------------------- [Average Check Sales] = DIVIDE ([Delivery Sales], [Transaction Count]) 5, "Average Check" --------------------------------------- [Active Unit Measures] = VAR MetricTypeFlagItem = SELECTEDVALUE(MetricTypeFlag[MetricTypeFlag]) VAR SelectedMeasure =     SWITCH(MetricTypeFlagItem   ...

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern  

https://exceleratorbi.com.au/show-or-hide-a-power-bi-visual-based-on-selection/  

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

  https://www.youtube.com/watch?v=n-OWNaCUU0o Steps: Duplicate Product table and name new table ProductUnpivot Select Product and Color in new table; make sure they are the same type; Unpivot. Now you have ProductKey, Attribute and Value columns in the new table. 1 to many relationship between Product and ProductUnivot Create slicer on ProductUnpivot[Attribute] - values are Product and Color Bar chart with ProductUnpivot[Value] Create new measure Dynamic Sales =  CALCULATE([Sales],  CROSSFILTER(Product[ProductKey], ProductUnpivot[ProductKey] ) Use CROSSFILTER instead of changing relationship Cross Filter Direction to BOTH

  https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

  RankOnSale = IF ( ISINSCOPE ( Products[Product] ), RANKX ( ALLEXCEPT ( Products, Products[Category] ), [SumOfSales] ), IF ( ISINSCOPE ( Products[Category] ), RANKX ( ALLSELECTED ( Products[Category] ), [SumOfSales] ) ) )

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

 PARALLELPERIOD - gets the total for the prior year Sales PY = CALCULATE (     [Sales Amount],     PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

 Year over Year % = VAR CYSales = [Sales Amount] VAR PYSales = [Sales SPLY]     -- sample period last year VAR Result = IF (     CYSales <> 0 && PYSales <> 0,     DIVIDE ( CYSales - PYSales, PYSales)     ) RETURN Result

 Sales Same Period Last Year Sales SPLY = CALCULATE(     [Sales Amount],     DATEADD (         'Date'[Date],         -1,         YEAR     ) Could also use SAMEPERIODLASTYEAR ( 'Date'[Date] )

Sales YTD =  CALCULATE (     [Sales Amount],     DATESYTD (         'Date'[Date]     ) )

 Dates on the rows in a year. month, day hierarchy. Calculate YTD Sales. Sales YTD = VAR LastVisibleDate = MAX( 'Date'[Date] ) VAR CustomYTD =     DATESBETWEEN (         'Date'[Date],         DATE( YEAR ( LastVisibleDate ),  1,  1 ),         LastVisibleDate     ) RETURN CALCULATE(     [Sales Amount],     CustomYTD  )

https://www.daxpatterns.com/parameter-table/   Use slicer to select whether to optionally divide sales amount by 1,000 or 1,000,000. Scale Table - show Scale string in slicer Scale = DATATABLE ( "Scale", STRING, "Denominator", INTEGER, { { "Units", 1 }, { "Thousands", 1000 }, { "Millions", 1000000 } } ) Measure - divide sales amount by Denominater for the slicer string selected. Use 1 if nothing selected or multiple selections. Sales Amount = VAR RealValue = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) VAR Denominator = SELECTEDVALUE ( Scale[Denominator], 1 ) VAR Result = DIVIDE ( RealValue, Denominator ) RETURN Result

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

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

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

  https://www.youtube.com/watch?v=0eYc-_KKTDs List your notifications in an Excel sheet that you load into the report.  Adds button that gets enable when there are notifications based on date threshold. Conditional navigation to page where a recent change was made.

https://www.youtube.com/channel/UCFp1vaKzpfvoGai0vE5VJ0w  

 Disable File, Options and settings,  Options GLOBAL, Data Load or CURRENT FILE, Data Load: Uncheck Auto date/time for new files