Using measure shows records that should be excluded (sample file included)

[DataSkills]

Hello, 

The attached PBIX file shows the issue. In essence, when using the measure (shown in the lower table), the slicer doesn't exclude any people. In the image below, I picked a specific facility (ID = 9), and as you can see, the top table shows only residents from that facility, whereas the lower table still shows residents from other facilities. 

I can solve the problem using a calculated column, but I am trying to NOT create a calculated column if I can help it. 

Click HERE to open the PBIX file. Then click the download button at top right to download the file. (See green arrow for download button). 

Thank you in advance. 

[Jihwan_Kim · Accepted solution]

Hi, 

I am not sure if I understood your semantic model correctly, but I suggest to try to start from the below measure. And then, I believe you can get to the goal that you need.

Weight Category Measure =
SWITCH (
    TRUE (),
    COUNTROWS ( Residents ) >= 1
        && ISBLANK ( MAX ( Residents[Weight] ) ), "No recorded weight",
    COUNTROWS ( Residents ) >= 1
        && MAX ( Residents[Weight] ) < 30, "Under 30kg in weight",
    COUNTROWS ( Residents ) >= 1
        && MAX ( Residents[Weight] ) > 130, "Over 130kg in weight",
    COUNTROWS ( Residents ) >= 1, "Normal weight"
)

[DataSkills]

Hi Jihwan_Kim, thank you for that solution. That seems to have done the trick! 🤗