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

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.

 

dedelman_clng's avatar
dedelman_clng
Community Champion
5 years ago

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