Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
marccahn
Frequent Visitor

Don't show data before user selects from slicer

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 visualFilter 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:  

marccahn_4-1712585945032.png

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 = 0Table 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:
marccahn_0-1712585333803.png

Value selected:

marccahn_1-1712585405142.png

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

Nothing selected:

 

marccahn_2-1712585471601.png

Added this to the filter of the main table visual:

marccahn_3-1712585528075.png

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

 
Any help would be GREATLY appreciated!
Marc Cahn
 
1 ACCEPTED SOLUTION
marccahn
Frequent Visitor

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!

View solution in original post

7 REPLIES 7
marccahn
Frequent Visitor

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

marccahn
Frequent Visitor

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
Super User
Super User

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"

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.

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.

@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]))

 

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...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.