Forum Discussion

pawelk3's avatar
pawelk3
Icon for Helper I rankHelper I
5 years ago
Solved

Slicer AND logic

Hi,   I have a dataset like below (example):     On the dashboard I want to have two identical tables that will be filtered with one slicer ('Skill' column used) with several options to ch...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    pawelk3 

     

    See if this works for you.

    First, the model:

     

    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:

     

     

     

    I've included the sample PBIX for your reference

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

 

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

 

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

 

pawelk3's avatar
pawelk3
Icon for Helper I rankHelper I
5 years ago

Anonymous, 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'.

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

 

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:

but I wish it would show these:

 

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?