topic Re: DAX remove filter and show missing record in DAX Commands and Tips

DAX remove filter and show missing record

Anonymous — Tue, 05 Oct 2021 12:00:22 GMT

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.

Re: DAX remove filter and show missing record

PaulOlding — Tue, 05 Oct 2021 13:07:56 GMT

Hi Anonymous

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.

Re: DAX remove filter and show missing record

Anonymous — Tue, 05 Oct 2021 15:34:51 GMT

Thanks PaulOlding, this solves the issue indeed.