Forum Discussion

E__'s avatar
E__
Icon for Helper III rankHelper 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 version, is as follows:

VISIT_ID            PRESTATION

1                       A

1                       B

1                       C

2                       A

2                       C

3                       B

3                       C

 

So in this case, I would want to return 1 for visit 2, but not for visits 1 and 3.

 

The measure for this is straightforward, I would use:

 

Measure =
VAR Visit = MIN(Table[VISIT_ID])
VAR PresA = CALCULATE(COUNTROWS(Table), 'Table'[Prestation] = "A", 'Table'[VISIT_ID] = Visit)
VAR PresB = CALCULATE(COUNTROWS(Table), 'Table'[Prestation] = "B", 'Table'[VISIT_ID] = Visit)
RETURN
IF(PresA = 1 && PresB = 0 , 1 , 0)
 
So far so good, but I would like to make this dynamic, using two slicers to select the values for A and B. Is there a way to use SELECTEDVALUE() in combination with two slicers, extracting the value from one slicer for the first variable (PresA) and the value from the other slicer for the second variable (PresB)?
 
Thanks in advance!
Best,
Eva
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    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
    PaulDBrown
    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.

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    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

  • E__'s avatar
    E__
    Icon for Helper III rankHelper III
    5 years ago

    Thank you both for your suggestions! This will definitely solve my problem.

     

    Best,

    Eva