Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
DAX
REMOVEFILTERS
show missing record
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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.

     

PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
4 years ago

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.