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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
sugra
Helper I
Helper I

Hide data in power bi visual until a filter was selected.

Good day,

 

I followed this post on hiding data in power bi unless a filter was selected. 

https://community.powerbi.com/t5/Desktop/How-can-I-hide-data-from-populating-Visuals-until-I-filter-... 

 

The solution worked perfectly in on my 1st report (with imported data). The steps were to:

1. Create a measure using HASONEFILTER option 

        e.g QuestionSelectCheck = IF(HASONEFILTER(SurveyQuestions[Question]),"1","2")

 

2. Add the measure to the filter for each visual where QuestionSelectCheck = 1

 

I tried the same method on a 2nd report (Direct Query). Although the measure works correctly, it does not hide data in my visual like in my 1st report. The visual does not seem to acknowledge the filter on the  QuestionSelectCheck measure.

 

Is it that measures as a filter only work on reports with imported data? Is there away around it? 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @sugra 

Due to I dont know your data model, and the result you want to get, I create a sample to have a test.

I build Table_1 in SQL Sever and get data in DirectQuery model.

Table_1:

1.png

2.png

Then I try your way:

 

QuestionSelectCheck2 =

IF(HASONEFILTER('Table_1'[X]),1,2)

 

Drag the measure into Filters on this visual and select 1.

3.png

Result is as below:

4.png

Select a:

5.png

My result is correct.

If you still cant use this way in DirectQuery, you can try my way.

 

Measure =

IF(ISFILTERED(Table_1[X]),1,BLANK())

 

OR

 

Measure =

IF(HASONEFILTER(Table_1[X]),1,BLANK())

 

NOTE: We need to keep all rows the same as below.

6.png

Result :

7.png

Select a:

8.png

If we use ISFILTERED we can make complex select:

9.png

If this reply still cant help you solve your problem, please provide me your data source, the visual of the result you want or the visual of the problem.

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/ES7Mp3eFL9JKgs6u...

 

Best Regards,

Rico Zhou

 

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

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @sugra 

Due to I dont know your data model, and the result you want to get, I create a sample to have a test.

I build Table_1 in SQL Sever and get data in DirectQuery model.

Table_1:

1.png

2.png

Then I try your way:

 

QuestionSelectCheck2 =

IF(HASONEFILTER('Table_1'[X]),1,2)

 

Drag the measure into Filters on this visual and select 1.

3.png

Result is as below:

4.png

Select a:

5.png

My result is correct.

If you still cant use this way in DirectQuery, you can try my way.

 

Measure =

IF(ISFILTERED(Table_1[X]),1,BLANK())

 

OR

 

Measure =

IF(HASONEFILTER(Table_1[X]),1,BLANK())

 

NOTE: We need to keep all rows the same as below.

6.png

Result :

7.png

Select a:

8.png

If we use ISFILTERED we can make complex select:

9.png

If this reply still cant help you solve your problem, please provide me your data source, the visual of the result you want or the visual of the problem.

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/ES7Mp3eFL9JKgs6u...

 

Best Regards,

Rico Zhou

 

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

lbendlin
Super User
Super User

in my test the second step fails as the filter options do not expose the enumerated values of the measure.  What worked for me was to replace all columns of the visual with measures that were controlled by the HASONEFILTER() measure.

Hi Ibelin, 

 

Thanks for taking the time to respond to my question.  I have many columns in my tables and need to keep them at the detail level (not aggregated). Could you give an example of how you did this?

 

"...replace all columns of the visual with measures that were controlled by the HASONEFILTER() measure..."

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.