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

PaulDBrown's avatar
PaulDBrown
Community Champion
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

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

    Big thanks also to Anonymous and dedelman_clng for your time and help.

     

    BR,
    Pawel