Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
E__
Helper III
Helper III

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
2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

PaulDBrown
Community Champion
Community Champion

@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:

Model.JPG

 

(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:

Multi select slicers Result.JPG

 

 

I've attached th PBIX file for you reference.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
E__
Helper III
Helper III

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

 

Best,

Eva

PaulDBrown
Community Champion
Community Champion

@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:

Model.JPG

 

(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:

Multi select slicers Result.JPG

 

 

I've attached th PBIX file for you reference.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.