Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Let me start with setting the setup of my model and data. Needless to say, this is a fictional modal, but it simulates the problem I am having in my actual PBI report. My actual report is based on a published dataset.
Model
Date dimension
PartyAnimal dimension
PartyRegistration fact
Below a mockup of the expected result of the report.
Expected result of report
The report has two slicers: on Month (of the Date dimension) and on BirthYear (of the PartyAnimal dimension).
I want to display two tables in the report. The first shows the names of the party animals and their fees. The table is filtered by the slicers, so it only shows the party animals that have registered in the selected month and of the selected birth year.
The second table should display all party animals that have registered in the selected month, but should ignore the selected birth year.
I have created a DAX expression to use in the second table:
CALCULATE([TotalFee], REMOVEFILTERS(PartyAnimal[BirthYear]))
This only gives me half of what I want. It does display the correct total, but it does not show the extra record. Below is the screenshot of the result of my report.
Actual result of report
My question
Is there a DAX expression that allows me to remove the filter context of a slicer, but also show the missing record?
I know that in this simple example it is possible to get the desired result with Edit Interactions to prevent the second table to interact with the BirthYear slicer. However, I would like to solve it with DAX to ensure reusability and because the original report is more complex.
Click here to download the PBIX.
Solved! Go to Solution.
Hi @jbas
This is auto-exist in action. As ever, sqlbi have a good article explaining what's happening: https://www.sqlbi.com/articles/understanding-dax-auto-exist/
In short, as Name and BirthYear are on the same table the engine does an optimization to not evaluate combinations that don't exist after the filter is applied.
The suggested workaround is to snowflake the dimension - ie to move BirthYear to a separate table.
You would hide BirthYear in the PartyAnimal table and use BirthYear from the BirthYear table in your slicer and measure
CALCULATE([TotalFee], REMOVEFILTERS(BirthYear[BirthYear]))
That gets you your desired result.
Hi @jbas
This is auto-exist in action. As ever, sqlbi have a good article explaining what's happening: https://www.sqlbi.com/articles/understanding-dax-auto-exist/
In short, as Name and BirthYear are on the same table the engine does an optimization to not evaluate combinations that don't exist after the filter is applied.
The suggested workaround is to snowflake the dimension - ie to move BirthYear to a separate table.
You would hide BirthYear in the PartyAnimal table and use BirthYear from the BirthYear table in your slicer and measure
CALCULATE([TotalFee], REMOVEFILTERS(BirthYear[BirthYear]))
That gets you your desired result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |