topic Re: Using measure shows records that should be excluded (sample file included) in DAX Commands and Tips

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

DataSkills — Wed, 07 Aug 2024 10:15:20 GMT

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.

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

Jihwan_Kim — Wed, 07 Aug 2024 11:42:10 GMT

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" )

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

DataSkills — Wed, 07 Aug 2024 11:52:51 GMT

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