cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Table not appearing for SELECTALL option

I have the following visualSelectAll_Table.png 

The table would appear only if a selection is made in all the 4 slicers. the DAX used is:

PSL Filter = if(isfiltered(Table[PSL]),1,0) and similarly for the remaining 3. Then i have set all 4 filters to be equal to 1 as a visual level filter for the table.

 

-> If a single selection is made for each filter then the table appears.

-> If multiple selections are made (As opposed to select ALL), the table appears.

 

However, to ease the use, when an end user selects the "SELECT ALL" option from the slicers, then the table is blanl. I am guessing it has something to do with the way I have written my DAX, but I am not sure. Any help here? Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Table not appearing for SELECTALL option


@Akshaya wrote:

So for now, with my DAX, it works for 1 and above. But when the user wanted to see for all the courses, he did a SELECT ALL on the slicer. But the table was still a blank. So I want the DAX to be written in such a way that it accounts for situations1 2 and 3. 

 


So the short answer is that there is nothing wrong with your DAX, it's just impossible to differentiate between "Select ALL" and select nothing in Power BI as it stands at the moment. As I said earlier:

 

 ...the issue is that semantically when you apply a filter to a column you are restricting the list of values returned. However when you do "Select All" you are not applying any restriction. So the UI optimizes away the "select all" and does not apply a filter to that column.

 


Power BI is built to be highly interactive and immediately show you the results of any slicer selections. You are trying to fight against the way Power BI is designed to work. I don't see any pure DAX solution to this. Off the top of my head I can see 4 possible work arounds.

 

  1. Remove the special visual filtering all together and just start off showing all jobs for all regions for all PSLs for all courses and let the normal filtering apply as users refine their selections.
  2. Apply a measure like the one I suggested in my post before this one that only shows results after users have picked values from 3 out of the 4 slicers.
  3. If you have Power BI Premium or Power BI Report Server (on-prem) you could build this page as a paginated report as they behave exactly in the way you want. Users have to pick all the parameter values, then click "run report" before they see any values
  4. You could do a more complicated solution using bookmarks buttons and a hidden table to track if the "apply filters" button is selected. 

ApplyFilters.gif

If you are interested to see how I built the Report in the image above I put a copy of it on my OneDrive here https://1drv.ms/u/s!AnSKp2UbblSYgcMDApVvW7fxki7O0g?e=ScoHPP The only issue is that once the user has hit "Apply Filters" then can then go back and untick their selections and effectively show an unfiltered table with all PSL/Region/Course/Job values - I don't know how much of an issue that is for you.

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Re: Table not appearing for SELECTALL option

What behaviour are you trying to achieve here?

 

The visual level filters you've described seem to be designed to force the users to select a subset of values from all 4 slicers. If you want the table to just show all the data that matches the slicer selections then deleting these visual level filters would do this.

Highlighted
Post Prodigy
Post Prodigy

Re: Table not appearing for SELECTALL option

@d_gosbell Hey,

 

- So if a user selects say 2-3 combinations under PSL/Region/Course, then the job filter automatically filters according to the selections made. Say there are only 7 jobs under these selections. Then a user would like to do just a SELECTALL as opposed to choosing each option one by one.

- Or a user may be intersted only in 2 of those jobs and he may just individually select on those 2

 

(The scanrio for any of the selection).

So I would like the table to appear for any of the scenarios - single selection of each slicer/ multiple/ select all.

 

However, for now the table appears for single/multiple selection but not for select all. So i am not sure as to how to change my dax accordingly. I need the dax as i want the table contents to appear only when selections are made on the 4 slicers, otherwise it is blank.

 

Hope I was able to explain that! Thanks!

Highlighted
Super User III
Super User III

Re: Table not appearing for SELECTALL option

So the issue is that semantically when you apply a filter to a column you are restricting the list of values returned. However when you do "Select All" you are not applying any restriction. So the UI optimizes away the "select all" and does not apply a filter to that column.

 


@Akshaya wrote:

 

So I would like the table to appear for any of the scenarios - single selection of each slicer/ multiple/ select all.

 


So does that mean you want the table to appear even when the user applies no filters?

 

If so then the easiest option is to just remove all the visual level filters checking for ISFILTERED on those slicers.

 

If you wanted to check that the user has filtered on at least 1 of the slicers then you could use a single measure like the following

 

OneOrMoreSlicersFiltered = IF( 
if(isfiltered(Table[PSL]),1,0) 
+ if(isfiltered(Table[Region]),1,0) 
+ if(isfiltered(Table[Course]),1,0) 
+ if(isfiltered(Table[Job]),1,0) > 1, 1,0)
Highlighted
Post Prodigy
Post Prodigy

Re: Table not appearing for SELECTALL option

@d_gosbell 

Hey.. let me try explaining again ..

 

All 4 slicers need to have at least one selection for the table to appear. Else the table is blank.

 

Now while filtering:

 

1. A user may either make one selection in each of the 4 slicers( one job title, one PSL, one region, one course)

2. Or a user makes one or more selections (Say he selects 4 job titles, one PSL, 2 regions and 2 courses)

3. Or the third scenario is say (he selects one psl, 2 jobs, 2 regions.. after making these choices there are a list of 7 courses. So now, the user may want to see details for all 7 courses and he may just want to do SELECT ALL, as opposed to clicking each of the 7 to make multiple choices - easier user interface basically).

 

So for now, with my DAX, it works for 1 and above. But when the user wanted to see for all the courses, he did a SELECT ALL on the slicer. But the table was still a blank. So I want the DAX to be written in such a way that it accounts for situations1 2 and 3. 

 

I hope i was able to explain better now. Thanks!

 

 

Highlighted
Super User III
Super User III

Re: Table not appearing for SELECTALL option


@Akshaya wrote:

So for now, with my DAX, it works for 1 and above. But when the user wanted to see for all the courses, he did a SELECT ALL on the slicer. But the table was still a blank. So I want the DAX to be written in such a way that it accounts for situations1 2 and 3. 

 


So the short answer is that there is nothing wrong with your DAX, it's just impossible to differentiate between "Select ALL" and select nothing in Power BI as it stands at the moment. As I said earlier:

 

 ...the issue is that semantically when you apply a filter to a column you are restricting the list of values returned. However when you do "Select All" you are not applying any restriction. So the UI optimizes away the "select all" and does not apply a filter to that column.

 


Power BI is built to be highly interactive and immediately show you the results of any slicer selections. You are trying to fight against the way Power BI is designed to work. I don't see any pure DAX solution to this. Off the top of my head I can see 4 possible work arounds.

 

  1. Remove the special visual filtering all together and just start off showing all jobs for all regions for all PSLs for all courses and let the normal filtering apply as users refine their selections.
  2. Apply a measure like the one I suggested in my post before this one that only shows results after users have picked values from 3 out of the 4 slicers.
  3. If you have Power BI Premium or Power BI Report Server (on-prem) you could build this page as a paginated report as they behave exactly in the way you want. Users have to pick all the parameter values, then click "run report" before they see any values
  4. You could do a more complicated solution using bookmarks buttons and a hidden table to track if the "apply filters" button is selected. 

ApplyFilters.gif

If you are interested to see how I built the Report in the image above I put a copy of it on my OneDrive here https://1drv.ms/u/s!AnSKp2UbblSYgcMDApVvW7fxki7O0g?e=ScoHPP The only issue is that once the user has hit "Apply Filters" then can then go back and untick their selections and effectively show an unfiltered table with all PSL/Region/Course/Job values - I don't know how much of an issue that is for you.

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors