Forum Discussion
How can I hide data from populating Visuals until I filter it in?
I do the same thing when reporting survey data. All visuals are blank until a user selects a question from a slicer. To do this, I first created a measure using IF and HASONEFILTER to check whether one question is selected:
QuestionSelectCheck = IF(HASONEFILTER(SurveyQuestions[Question]),"1","2")
Then, for each visual, I drag that into the visual level filters box and set it to display if QuestionSelectCheck = 1.
Note that this doesn't remove everything (headers and titles and what not are still there for each visual), but it does remove all the data from the visual.
Depending on your specific needs, you may need HASONEVALUE, ISFILTERED, or even a COUNTA/DISTINCTCOUNT instead of or in addition to HASONEFILTER. See http://www.powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/.
I do the same thing when reporting survey data. All visuals are blank until a user selects a question from a slicer. To do this, I first created a measure using IF and HASONEFILTER to check whether one question is selected:
QuestionSelectCheck = IF(HASONEFILTER(SurveyQuestions[Question]),"1","2")
Then, for each visual, I drag that into the visual level filters box and set it to display if QuestionSelectCheck = 1.
Note that this doesn't remove everything (headers and titles and what not are still there for each visual), but it does remove all the data from the visual.
Depending on your specific needs, you may need HASONEVALUE, ISFILTERED, or even a COUNTA/DISTINCTCOUNT instead of or in addition to HASONEFILTER. See http://www.powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/.
I am using a Table visual of Employees. It doesn't have any measures in it, just columns. Each employee has a row for each year they have history.
I have a Smart Filter slicer visual that allows the user to type in an employee name and filter the details table by that employee. I would like the table to be empty until an employee name is selected in the slicer.
I have created a filter selected measure:
Employee Is Filtered = if(HASONEFILTER(Employee[Employee Name]) || HASONEFILTER(Employee[Employee Number]),1,0)
Since the details table isn't aggregating the employee data, each row returns 1 for this measure (the "grand total" row of the table shows 0). So the table shows all employees until an employee is selected in the slicer, rather than not showing any employees.
I was quite happy to find this post and try out the technique here, but it looks like it only works for visuals that aggregate details.
Any ideas to make this work?
Mike
Mike,
I have used an alternate method using ALLSELECTED to do the same thing. ALLSELECTED forces the measure to disregard the table's row/column context. Something like this:
Employee Is Filtered = IF(CALCULATE(COUNTA(Employee[Employee Name]), ALLSELECTED(Employee[Employee Name]))=1,1,0).
See http://tinylizard.com/all-vs-allselected/ for more details.
That's still not quite working.
The problem I believe is that the Table's visual filter is evaluating the measure for each row displayed in the table, not in the context of the collective rows in the table.
Maybe try ALLSELECTED with the table reference instead of the column? Like this: ALLSELECTED[Employee]