topic Re: Hide rows with blank values for measure depending on slicer selection in DAX Commands and Tips

Hide rows with blank values for measure depending on slicer selection

RobinNeven — Mon, 21 Dec 2020 13:00:11 GMT

Hello,

In this pbix (follow link) there are two pages: page 1 holds 3 tables and one slicer, all populated through the 'fact table' called Planning, and showing the desired behavior. Page 2 holds the exact same content, but with the 'Group' column being replaced by GroupID, which holds the same values but coming from a separate dimension called 'GroupDimension'. In the latter case, all of a sudden the table on the far right does not show the desired behavior anymore, but instead also shows rows with blank values for the status selected in the 'Status' slicer.

How do I get the far right table on Page 2 to display the same behavior as on Page 1? I think it is a DAX issue so I tried playing around with ALLEXCEPT, ALLSELECTED and HASONEVALUE in the 'Waiting' and 'Running' measures, but to no avail. HASONEVALUE got me half-way, because it showed the correct thing when a value is selected in the slicer, but then it doesn't show everything I want it to show when nothing/all from the slicer is selected (I don't know why because there is not one value selected then but two, so it shouldn't return anything in my opinion but okay).

Thank you very much in advance for any help!

Re: Hide rows with blank values for measure depending on slicer selection

AlB — Mon, 21 Dec 2020 19:36:43 GMT

Hi RobinNeven

Update your measures

Running = VAR CountRunning = CALCULATE(COUNTROWS(Planning), Planning[Status] = "Running") RETURN IF(COUNTROWS(Planning) > 0, CountRunning)

Waiting = VAR CountWaiting = CALCULATE(COUNTROWS(Planning), Planning[Status] = "Waiting") RETURN IF(COUNTROWS(Planning) > 0, CountWaiting)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Hide rows with blank values for measure depending on slicer selection

RobinNeven — Tue, 22 Dec 2020 12:34:21 GMT

Wow, such a simple solution for something I've been struggling so long with! I was way overthinking it apparently, thanks!

Re: Hide rows with blank values for measure depending on slicer selection

RobinNeven — Wed, 23 Dec 2020 12:53:05 GMT

My apologies, but it turns out I was looking at the wrong page (page 1), which lead me to wrongfully conclude that your solution worked. On page 2, the problem still persists with the new measure definitions you provided (the blank value for measure 'Waiting' is still displayed when I select 'Running' in the slicer, and the blank value for measure 'Running' is still displayed when I select 'Waiting' in the slicer, which I don't want).

I'm looking forward to any other solutions!

Re: Hide rows with blank values for measure depending on slicer selection

AlB — Wed, 23 Dec 2020 12:59:49 GMT

RobinNeven

Not sure what you did. I get exactly the same results on page 1 and page 2.

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers