DAX remove filter and show missing record
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.
