Granularity with dynamic range limitation

gumis_rulez — Thu, 31 Oct 2024 13:48:53 GMT

On the left side I have slicer for granularity - made with additional table connected with relationship in both directions.

On the right side I have slicer which limits the range of data.

How to combine those two solutions so that they would work?

https://drive.google.com/file/d/1XwIhdvOdNRbNfQNx9ws-EzrTXT_OkNy2/view?usp=sharing

Re: Granularity with dynamic range limitation

DataInsights — Thu, 31 Oct 2024 20:24:10 GMT

gumis_rulez,

One approach is to use a field parameter for the D/W/M/Q/Y slicer. Use the field parameter in the x-axis to enable dynamic selection of granularity.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Re: Granularity with dynamic range limitation

gumis_rulez — Tue, 05 Nov 2024 13:03:08 GMT

The granularity works: D/W/M/Q/Y is a slicer based on the table:

hlp_Dynamic Date Selections = UNION( ADDCOLUMNS( CALENDAR( MIN(dimCalendar[Date]), MAX(dimCalendar[Date]) ), "Visual Date", [Date], "Type", "D", "Order", 1 ), ADDCOLUMNS( CALENDAR( MIN(dimCalendar[Date]), MAX(dimCalendar[Date]) ), "Visual Date", [Date] - WEEKDAY([Date], 2) + 1, "Type", "W", "Order", 2 ), ADDCOLUMNS( CALENDAR( MIN(dimCalendar[Date]), MAX(dimCalendar[Date]) ), "Visual Date", YEAR([Date]) & "-" & MONTH([Date]), "Type", "M", "Order", 3 ), ADDCOLUMNS( CALENDAR( MIN(dimCalendar[Date]), MAX(dimCalendar[Date]) ), "Visual Date", YEAR([Date]) & "-Q" & CEILING(MONTH([Date])/3, 1), "Type", "Q", "Order", 4 ), ADDCOLUMNS( CALENDAR( MIN(dimCalendar[Date]), MAX(dimCalendar[Date]) ), "Visual Date", YEAR([Date]), "Type", "Y", "Order", 5 ) )

Question is how to combine:

Date Param = CALCULATE( [Sales], KEEPFILTERS( DATESBETWEEN( dimCalendar[Date], [Selected Min Date], [Max Date] ) ) )

where

Selected Min Date = VAR _MaxDate = [Max Date] VAR _MinDate = [Min Date] - 1 RETURN SWITCH( 'hlp_Date Selection'[Selected Date Selection], "1W", _MaxDate - 7, "1M", EDATE(_MaxDate, -1), "3M", EDATE(_MaxDate, -3), "TY", DATE(YEAR(_MaxDate), 1, 1), "1Y", EDATE(_MaxDate, -12), "ALL", _MinDate )

and

Max Date = CALCULATE( MAX(dimCalendar[Date]), REMOVEFILTERS(dimCalendar[Date]) )

to automatically limit the range. If I choose 3M to limit the displayed data to three months?

topic Re: Granularity with dynamic range limitation in DAX Commands and Tips

Granularity with dynamic range limitation

Re: Granularity with dynamic range limitation

Re: Granularity with dynamic range limitation