DAX Measure not working as expected when applied as filter for table

[ValeriaBreve]

Hello,

disclaimer: I am not good at DAX!!!

So here's my issue: I want to hide dynamically a table when nothing is selected (in my case, "ProjectID" field).

So I created a measure to understand if my ProjectID is selected or not:

ProjectSelectCheck = IF(HASONEVALUE('MyTable'[ProjectID]),2,BLANK())

using "hasonevalue" and the ProjectID selected comes from another linked table so I need the cross-filtering power. It works well and as expected:

 when nothing is selectedwhen a project is selected

So next I am adding this measure as a filter to my table (visual) that has all ProjectIDs (I just left the ProjectID from the same table to test) and selecting ProjectSelectCheck=2.

I am then making sure that I uncheck "Show Items with no data" and I am not summarizing anything.

And.... nothing happens (visible at least). The measure is not working as expected, I still have all my ProjectIDs there.

When I check the filters for the table, I don't see anything else than the check so nothing to report there.

And if I add the Measure to the table, it actually reads "2"! see screenshot below - card with the measure and measure coming from the table where I have my column with ProjectIDs.

Does anybody have an idea what I am doing worng here? I can't understand this behaviour.

Thanks!

Kind regards

Valeria 

[v-jianboli-msft · Accepted solution]

Hi @ValeriaBreve ,

Then function HASONEVALUE returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.

Since your table visual retains the ProjectID, when you apply Measure to this visual, its context is actually affected.

What kind of effect are you trying to achieve and what determines whether a ProjectID is filtered or not?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValeriaBreve]

Hello,

I understand now, thanks! The target for me is to hide dynamically a table (containing ProjectIDs) when no ProjectID is selected. But from you are saying, if I use HASONEVALUE in conjuction with the table that has my ProjectIDs, it will not work as it gets applied to every row, which of course has a single ProjectID number, and this is why it returns the value of the single ID number and not BLANK(). Logical!

I then got around the issue by using "DISTINCTCOUNT" on the ProjectIDs, to only return a number when the result is 1 (BLANK() otherwise). That works well for my purpose.

Thanks!