topic Re: Double Slicer Selection and Comparison through Months in DAX Commands and Tips

Double Slicer Selection and Comparison through Months

manosjxmaah — Fri, 26 Apr 2024 13:02:53 GMT

Hello!

I have two dropdown slicers in my page, that the user uses to select two different years. Then i want to have a matrix that calculates the percentage difference for each month.

My model -that concerns us here- is this:

As you can see, i have a DateTable that contains all dates and a CompareDates table that is exactly the same as DateTable. That is so I can have two slicers on the date and also have an inactive relationship.

The problem occurs in the below scenario:

When an Abbr Group doesn't exist in the DateTable period selected by the user but it exists in the CompareDates period selected. For example, Abbr Group "ABC" exists in the year 2023 but it doesn't exist in the year 2024.

Suppose that in 2024 we got these Abbr Groups: "ABC", "DFG" and in 2023 these: "ABC", "DFG", "XYZ"
The resulting matrix should be:

Abbr Group | sumAvg Availability in Month 2024 | sumAvg Availability in Month 2023 |

ABC | 10 | 13 |

DFG | 5 | 4 |

XYZ | | 3 |

In the code bellow, I make some Variables that I get from the filter context of the matrix (which has Abbr Group in the columns)

VAR _year = MAX(DateTable[uYear]) VAR _month = MAX(DateTable[uMonth]) VAR _quarter = MAX(DateTable[QuarterText]) VAR abbr_group = MAX('04 Reg'[Abbr Group]) VAR compare_year = MAX(CompareDates[uYear]) VAR currentYear = YEAR(TODAY()) VAR currentMonth = MONTH(TODAY()) VAR currentYearSelected = IF(_year = currentYear, TRUE(), FALSE()) VAR year_denomenator = IF( currentYearSelected, MONTH(TODAY()), 12 )

I use these variables here:

VAR avg_avail_per_per_month = SUMMARIZE( ALL('Units'), '04 Reg'[Abbr Group], Units[MonthName], "Avg Availability in Month", CALCULATE( SUM(Units[Avg Availability in Month]), Units[_Year] = compare_year, Units[Month Number] <= currentMonth, '04 Reg'[Abbr Group] = abbr_group ) ) VAR total_of_totals = SUMX(avg_avail_per_per_month, [Avg Availability in Month] / year_denomenator) RETURN total_of_totals

The problem i suppose, is that the fact that because the year selected from DateTable is 2024, when I use the

VAR abbr_group = MAX('04 Reg'[Abbr Group])

code, I don't get the appropriate Abbr Group.

What can I do please?

Re: Double Slicer Selection and Comparison through Months

lbendlin — Sat, 27 Apr 2024 01:26:50 GMT

What you are showing isn't really a data model. You may want to refactor that.

Consider using a single "Between" slicer where you then grab the MIN and MAX values for your comparison years.

Re: Double Slicer Selection and Comparison through Months

manosjxmaah — Sun, 28 Apr 2024 21:20:56 GMT

Thanks for the reply and sorry for the late answer!

What do you mean that it isn't a data model? Do you any articles to suggest that talk about building the datamodel in pbi? I am familiar with star/snowflake/constallation schemas, since I have worked a bit with SSAS, but I don't really know if they are much useful in pbi.

Also, I don't really want to use a between slicer, since I include a date hierarchy in my dropdowns which are what my users are used to. Do you have any suggestions on how I can do what i need by adjusting my DAX?

Re: Double Slicer Selection and Comparison through Months

lbendlin — Sun, 28 Apr 2024 21:36:39 GMT

Exactly - Power BI prefers Star Schema data models. Snowflakes are tolerated but the guidance is to flatten these.

Keep in mind that the Power BI user interface includes a Filter Pane. That gives you much more powerful filtering and it frees up screen real estate. " which are what my users are used to. " is a fair argument, but one that needs to be challenged.