cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Slicer AND logic

Hi,

 

I have a dataset like below (example):

 

200927_17-17-22_cp_example.xlsx_-_Excel.png

 

On the dashboard I want to have two identical tables that will be filtered with one slicer ('Skill' column used) with several options to choose from. Table 1 should have an additional filter 'Priority = 3', Table 2 should filter 'Priority = 2'.

 

What I want to achieve:

(1) when the slicer doesn't have any options selected, both tables should be empty;
(2) possibility to choose several skills in the slicer;

(3) when I choose one skill in the slicer, the tables should show all rows from the dataset for positions with this skill and, depending on the table - with a priority of 3 or 2;
(4) when adding each subsequent skill in the slicer, the table should show records only for those positions that have only the selected skills and, depending on the table, priority 3 or 2.

I would be very grateful if someone could help me with this. If it is not clear, please ask additional questions, I will try to explain.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Slicer AND logic

@pawelk3 

 

See if this works for you.

First, the model:

Model.JPG

 

Then create a measure to use in the filter pane of the table/matrix:

 

Filter measure = 
VAR skills = CALCULATE(COUNTROWS(ALLSELECTED('DIM Skill'[Skill])))
VAR Position = SUMX(
    SUMMARIZE('Fact Table', 'Dim Position'[Position], 'DIM Skill'[Skill], 'Dim 
               Priority'[Priority]), 
        CALCULATE(DISTINCTCOUNT('Fact Table'[Skill]),  
            ALLEXCEPT('Fact Table', 'Dim Position'[Position], 'Dim Priority'[Priority])))
RETURN
IF(skills = 1, 1, IF(skills = Position, 1))

 

And you get this result:

1 filter.JPG

 

Result.JPG

 

No filters.JPG

 

I've included the sample PBIX for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

9 REPLIES 9
Highlighted
Memorable Member
Memorable Member

Re: Slicer AND logic

hi @pawelk3 - you can use a Skills slicer as required to filter your report based on Skills, you can enable multi select for the slicer as show below

Sumanth_23_1-1601234634852.png

 

For filtering Table 1 and Table 2 to display only priority 3 and 2 respectively you can apply visual level filters as shown in the below screenshot

Sumanth_23_0-1601234508790.png

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

 

Highlighted
Helper I
Helper I

Re: Slicer AND logic

@Sumanth23, thank you for your answer. 

I know that in case of priority I can apply a filter for a specific visual and I did that. I would like the lack of a selected option in the slicer to show only the table headers. Currently, the table shows all rows for the condition 'Priority = 3'.

pawelk3_1-1601236698122.png

The second issue is to change the slicer logic from OR to AND. I probably did not explain my problem well enough. After selecting one skill from the slicer (Skill 1), the table should show all the rows that contain that skill (see below).

 

pawelk3_2-1601236975595.png

The problem I struggle with is choosing more than one skill in the slicer. After selecting two (Skill 1 and Skill 2), the table shows these rows:

pawelk3_3-1601237241423.png

but I wish it would show these:

pawelk3_4-1601237425819.png

 

Because only 'Position 3' has both of these skills with priority 3. I would like to achieve the same effect by selecting 3 skills and more at the same time. Is it somehow possible?

 

 

Highlighted
Memorable Member
Memorable Member

Re: Slicer AND logic

hi @pawelk3 - I now get what you are looking to achieve with the slicers.

#1 Not selecting any values in the slicer is equivalent to "select all" and hence it shows all data - there is no default setting to hide data unless value is selected from the slicer. 

You can try and implement this logic using the steps mentioned in the link below: 

https://www.kasperonbi.com/show-values-in-a-chart-only-when-a-slicer-is-selected/

 

You can refer to the steps mentioned in the link below to setup the AND criteria on the slicer as you require - this would need to be done using DAX: 

https://radacad.com/slicer-with-and-condition-in-power-bi

 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks! 

Highlighted
Helper I
Helper I

Re: Slicer AND logic

@Sumanth_23, thanks for the suggestions, I am trying to change the slicer logic with the tutorial you pointed me out (https://radacad.com/slicer-with-and-condition-in-power-bi) but I have a problem with that.

This is my simple model:

pawelk3_0-1601286825606.png


And the measure I created based on tutorial:

 

Chosen Skills with AND-Only Condition = 
var selectedSkills=VALUES(tab_umi[Skill])
var countRowsFact=
COUNTROWS(
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                tab_bda,
                RELATED(tab_umi[Skill]) in selectedSkills
                ),
                "Skill",
                RELATED(tab_umi[Skill])
        )
    )
)
var countRowsSkills=COUNTROWS(selectedSkills)
return
IF(countRowsFact>=countRowsSkills,countRowsFact)

 

 

When you select one skill from the slicer, everything is fine, in the case of two or more skills, the table returns no results. Where is the mistake?

 

200928_11-57-38_Untitled_-_Power_BI_Desktop.png

 
 

I will be very grateful if someone can help me solve this problem. I have been struggling with it for the second day, without much results.

 

Highlighted
Super User I
Super User I

Re: Slicer AND logic

@pawelk3 please link your pbix in the thread and I will get some more help on this.




Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Slicer AND logic

Hi @dedelman_clng, here is the link to .pbix file.

Highlighted
Super User I
Super User I

Re: Slicer AND logic

Hi @pawelk3 - this blog post was pointed out to me. I haven't worked through it yet, but if you wanted to give it a shot go ahead

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130 

 

David




Proud to be a Super User!




Highlighted
Super User II
Super User II

Re: Slicer AND logic

@pawelk3 

 

See if this works for you.

First, the model:

Model.JPG

 

Then create a measure to use in the filter pane of the table/matrix:

 

Filter measure = 
VAR skills = CALCULATE(COUNTROWS(ALLSELECTED('DIM Skill'[Skill])))
VAR Position = SUMX(
    SUMMARIZE('Fact Table', 'Dim Position'[Position], 'DIM Skill'[Skill], 'Dim 
               Priority'[Priority]), 
        CALCULATE(DISTINCTCOUNT('Fact Table'[Skill]),  
            ALLEXCEPT('Fact Table', 'Dim Position'[Position], 'Dim Priority'[Priority])))
RETURN
IF(skills = 1, 1, IF(skills = Position, 1))

 

And you get this result:

1 filter.JPG

 

Result.JPG

 

No filters.JPG

 

I've included the sample PBIX for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Highlighted
Helper I
Helper I

ulRe: Slicer AND logic

@PaulDBrown, it is working like a charm. Thank you for the solution!

Big thanks also to @Sumanth_23 and @dedelman_clng for your time and help.

 

BR,
Pawel

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (965)