Power BI Tips

  https://www.daxpatterns.com/like-for-like-comparison/

Unit Fiscal Week Sales =   FILTER( ADDCOLUMNS( SUMMARIZE( 'Financial Summary', 'Unit'[UnitId], 'Calendar'[Fiscal Week End Date] ), "Weekly Sales", [Sales] ), [Weekly Sales] > 0 ) -- query to determine the Units that have sales > $0 in every week of the report date range DEFINE      VAR Units =          CALCULATETABLE (              VALUES ( Unit[UnitId] ) ,              Unit[Market] = "Domestic" ,     -- report filter              Unit[Brand] = "Carl's Jr."      -- page filter: "Carl's Jr." or "Hardee's"          )      VAR ReportDateRange =          ...

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

  https://www.sqlbi.com/articles/when-to-use-keepfilters-over-iterators/

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/  

https://www.youtube.com/watch?v=Zhp1fAfN8Hw   https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/ Example of how to take a column from a dimension and render it in the VALUES area of a matrix. In one case uses CROSSFILTER.

  https://hatfullofdata.blog/paginated-report-resources/

  https://www.youtube.com/watch?v=Hqs0lJJq1YA https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/ Shows how to move a filter using a physical relationship (fastest), TREATAS, INTERSECT or CONTAINS.

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/  

  https://radacad.com/scenarios-of-using-calculated-tables-in-power-bi

https://www.sqlbi.com/whitepapers/many2many/  

https://www.sqlbi.com/articles/from-sql-to-dax-filtering-data/  

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

  https://www.daxpatterns.com/comparing-different-time-periods/

https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel  

https://www.havensconsulting.net/blog-and-media/creating-sliding-toggles-with-native-buttons  

https://www.youtube.com/watch?v=4emPkBiiCtg&t=123s  

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

  RM Average Sales for Units With Sales > $0 = -- Get the UnitIds. VAR Units = VALUES('Unit'[UnitId]) -- Get the Fiscal Week Num Overall for the Current Fiscal Week. This will be -- an incomplete week (we always want the last completed week). On the Tuesday -- morning refresh, the Fiscal Week Num Overall will increment to the next week -- which has just begun. VAR FiscalWeekNumber = CALCULATETABLE(     SUMMARIZE(     'Calendar',     'Calendar'[Fiscal Week Num Overall]     ), ALL('Calendar'),     'Calendar'[Current Fiscal Week] = TRUE() ) -- Get the list of the last 9 Fiscal Week End Dates prior to the current one. -- This is the date range to check for Sales > $0. VAR FiscalWeekEndDates = CALCULATETABLE(     SUMMARIZE( 'Calendar', 'Calendar'[Fiscal Week End Date]     ), ALL('Calendar'),     AND(...

  https://blog.pragmaticworks.com/powerbi-dax-variables-calculate-and-filter Call CALCULATE while creating a calculated column. Use FILTER to create a table filter for CALCULATE.

  https://powerbi.microsoft.com/en-us/blog/

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

  https://www.youtube.com/watch?v=-ip7mKUdwRg Includes short blurb on connecting Power BI Datasets to your own data in the model

  https://www.youtube.com/watch?v=g-nRxDVt3To New in Dec 2020 release of Power BI, you can combine a tabular model with import. https://www.sqlbi.com/articles/new-composite-models-in-power-bi-a-milestone-in-business-intelligence/

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-services   With  DirectQuery for Power BI datasets and Azure Analysis Services (AAS) , you can use DirectQuery to connect to AAS or Power BI datasets and if you want, combine it with other DirectQuery and imported data. Report authors who want to combine the data from their enterprise semantic model with other data they own, such as an Excel spreadsheet, or want to personalize or enrich the metadata from their enterprise semantic model, will find this feature especially useful.

https://www.sqlbi.com/articles/relationships-in-power-bi-and-tabular-models/  

  https://www.youtube.com/watch?v=AieQMS6rGvM https://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/   Preferred solution: Calendar   5   = VAR   BaseCalendar  =      CALENDAR   (   DATE   (   2016 ,   1 ,   1   ) ,   DATE   (   2018 ,   12 ,   31   )   ) RETURN      GENERATE   (          BaseCalendar ,          VAR   BaseDate  =   [Date]          VAR   YearDate  =   YEAR   (   BaseDate  )          VAR   MonthNumber  =   MONTH   (   BaseDate  )          VAR   MonthName  =   FORMAT   (   BaseDate ,   "mmmm"   ) ...

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/  

https://www.sqlbi.com/articles/introducing-summarizecolumns/   still not fully supported in a modified filter context. For example, it cannot be used within a measure in an expression of  ADDCOLUMNS .

  https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

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

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

  https://www.youtube.com/watch?v=VXd1wl-5vKI

  https://www.youtube.com/watch?v=DKgF-5QHY68

https://www.sqlbi.com/articles/creating-table-functions-in-dax-using-detailrows/  

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/  

  https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

  https://www.youtube.com/watch?v=tONOosN1xAs https://www.sqlbi.com/articles/apply-and-logic-to-multiple-selection-in-dax-slicer/

  https://www.youtube.com/watch?v=l5JX3G5Ntzk https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/ Set-Based Solution: Create table to be used as a filter in CALCULATE: UNION Crossjoin the selections in the Product[Brand] slicer with any Customer[Occupation] Crossjoin the selections in the Customer[Occupation] slicer with any Product[Brand] OR   # 1   := CALCULATE   (      [Units] ,      UNION   (          CROSSJOIN   (   VALUES   (   'Product'[Brand]   ) ,   ALL   (   Customer[Occupation]   )   ) ,          CROSSJOIN   (   ALL   (   'Product'[Brand]   ) ,   VALUES   (   Customer[Occupation]   )   )      ) )

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

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

  https://www.youtube.com/watch?v=lkHFpmA4SJ4 In this Power BI Tutorial, Patrick looks at how to dynamically filter by today's date. Using a date table, you can create a DAX measure that can then be used as a Power BI report-level filter to dynamically filter by today's date within Power BI Desktop

https://www.daxpatterns.com/comparing-different-time-periods/ Compare measure amounts using 2 different time periods. Add a new Date table (e.g. Comparison Date) that is a duplicate of the existing date table.  The relationship between Date and Comparison Date is Many-to-1 and Inactive. Cross filter direction = Single. Activate the relationship in the measure. Add 2 slicers; one uses Date table, the other uses Comparison Date table. Compare measures using the 2 Date tables. Comparison Sales Amount = CALCULATE([Sales Amount], REMOVEFILTERS('Date'),  USERELATIONSHIP('Date'[Date], 'Comparison Date'[Comparison Date]) )

  https://www.youtube.com/channel/UCjlfQwqb-0S40XQ8seYPLSw

  https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/   Create measures that you can put in the filter for slicers to limit the items in the slicer. Just check whether the table is NOT empty. For instance, you can filter a slicer on Color to just the colors that have actually been sold by checking whether the Sales table is NOT empty. MEASURE:  Slicer Sales = INT (NOT ISEMPTY ( Sales )) Add measure to the Filters on this visual for the slicer and specify Show items when the value is 1

  https://www.youtube.com/watch?v=DKgF-5QHY68 https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/

  https://www.youtube.com/watch?v=wQ9DohPvx8Q Use case is to show sales for the last 12 months and the sales forecast for the next 12 months. Add a calculated column (e.g. 24 Month Reports) to the Calendar table to indicate whether the date is in the range of previous 12 months or next 12 months. The value will be 1 if TRUE or 0 if FALSE. Filter the visual to check if 24 Month Reports is 1.

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

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/  

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

  https://www.youtube.com/watch?v=o8K4Px_6AYQ Add measure to Filters on the slicer visual; e.g. MEASURENAME > 0