Forum Discussion

E__'s avatar
E__
Helper III
5 years ago
Solved

Extracting SELECTEDVALUE() from two slicers

Hi, I'm trying to make a measure that returns 1 for all visits that have a related prestation, let's say A, but don't have another certain prestation, let's say B. The data, in a simplified versio...
  • Greg_Deckler's avatar
    5 years ago

    E__ You will need 2 disconnected slicer tables to run your slicers. Then you can use SELECTEDVALUE to grab the value selected.

     

    You can create these slicer tables using:

    Slicer Table 1 = DISTINCT(SELECTCOLUMNS('Table',"PRESTATION",[PRESTATION]))

     

    Slicer Table 2 = DISTINCT(SELECTCOLUMNS('Table',"PRESTATION",[PRESTATION]))

     

    After you create them, go to the model view and make sure that any relationships to these tables are removed

  • PaulDBrown's avatar
    5 years ago

    E__ 

     

    Following on from Greg_Deckler  suggestion, if you want to allow for a multi-selection in either slicer, you can do the following.

    First the model:

     

    (Just FYI, this will also work if you have Slicer 1 related to your fact table in a one-to-many relationship)

    Next, create a measure as follows:

     

    Countrows non-coinciding "Prestations" = 
    VAR Pres1 = CALCULATETABLE(VALUES('Fact'[VISIT_ID]), 
                TREATAS(VALUES('Slicer 1'[Prestation1]), 'Fact'[ PRESTATION]))
    VAR Pres2 = CALCULATETABLE(VALUES('Fact'[VISIT_ID]), 
                TREATAS(VALUES('Slicer 2'[Prestation2]), 'Fact'[ PRESTATION]))
    RETURN
    COUNTROWS(EXCEPT(Pres1, Pres2))

     

     

    And you will get this:

     

     

    I've attached th PBIX file for you reference.