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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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