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

[sugra]

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? 

[v-rzhou-msft · Accepted solution]

Hi @sugra 

Due to I don’t 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:

Then I try your way:

QuestionSelectCheck2 =

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

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

Result is as below:

Select a:

My result is correct.

If you still can’t 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.

Result :

Select a:

If we use ISFILTERED we can make complex select:

If this reply still can’t 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]

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.

[sugra]

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