Forum Discussion

pawelk3's avatar
pawelk3
Helper 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
    Helper 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?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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! 

      • pawelk3's avatar
        pawelk3
        Helper I
        5 years ago

        Anonymous, 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:


        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?

         



         
         

        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.