Don't show data before user selects from slicer

[marccahn]

In a report, I need to hide the data in a table visual until users selects a value from the slicers.  They NEVER need/want to see all the data.  I have seen it done and it looks so easy!  The report only has a single data souce called EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V and the column in the slicer is called SERVDATE

1 - I created a measure on the table with this formula:

Filtered = int(ISFILTERED(EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V[SERVDATE]))

2 - I added the measure as a filter to the table visual -

Filter added to the table visual

I placed the measure on a card so that I can see it's value and it showing correctly - 0 when nothing is selected and 1 when a value is selected.  This is the card when no selection is made:  

I would expect that the table would hide the data with this value but the table data is visible 

A couple of hints -

1 - It all works when I hard code the values in the measure instead of using the IsFiltered DAX code.  This code hides the data on the table exactly as I need it to:

Filtered = 0

This shows the data:

Filtered = 1

While this does not do what is needed:

Filtered = int(ISFILTERED(EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V[SERVDATE]))

2 - I placed the measure on the table so that I can see the vaule from that perspective and it is never 0 - it always is 1.  Table show measure with the value of 1 despite the card showing the value = 0

I created table with a new measure with this formula:

Filtered on diff table = int(ISFILTERED(EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V[SERVDATE]))

I used a card to see both measure values -

Value in slicer not selected:

Value selected:

A table visual of this new table shows the correct value -

Nothing selected:

Added this to the filter of the main table visual:

The data still shows when the value of the new measure is 0!

Any help would be GREATLY appreciated!

Marc Cahn

[marccahn]

@lbendlin, please see my post above where I explained how I got the functionality to work.  Thanks for your assistance!

[marccahn · Accepted solution]

I found the answer! It seems to matter where you attach the measure you want to use for this functionality. I have one main data source - a Snowflake SQL view. This did not work when I attached the measure to that data source even when the measure seemingly correctly indicated whether the slicer data item was filtered or not.

I created a unique table with the column that I needed to have in the slicer and attached the measure to that created table and it worked perfectly!

[lbendlin]

Design red flag.

Remember that in Power BI "nothing selected"  is (mostly) equivalent to "everything selected".  There's a small difference between "filter hasn't been touched yet" and "nothing selected"

[marccahn]

Thank you for that correction, @lbendlin .  I still need to add that functionality to the report.  I believe that my methodology is correct (please tell me if that is not the case), but I cannot get the report to function as I require.

[lbendlin]

The difference in values is caused by different filter contexts. You can use a measure as a filter, but only on an individual visual, not on an entire page, for exactly that reason.

[marccahn]

@lbendlin, thanks for the quick reply and that makes sense.  I am using the measure as a filter on the individual visual – not the page.  As I stated, with the filter on the visual, I can show or hide the data in the table if I hardcode the measure with a 0 (hide) or 1 (show).  I don't understand why this DAX code doesn't work:

Filtered = int(ISFILTERED(EBI_USER_DETAIL_FINANCE_SAP_EXPORT_V[SERVDATE]))

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...