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
JFG123
Frequent Visitor

Create measure that is dependent on slicers in table

I would like to create a measure that can be used in a table as a benchmark or comparison point. However, the measure needs to be dependent on end user slicer selection.

 

For example, I have collected data on a number of car owners, the cars they have and the colour. Then I have done a series of tests (Test A,b,c). I want to create the comparison value calculation that calculates the max by for each test type depending on what is used in the selection boxes (highlighted in yellow), i.e. if only Yarris and the color red are selected, the comparison measure should return the max value for just that selection (and not take into account name). In the screenshot, the assumption is that all slicer options are selected. 

JFG123_0-1639620194730.png

 

I have attached sample data in a pbix here: https://drive.google.com/file/d/1ib0FAGD-6xt_GueJs-aWzCBtI2bAV9Hm/view?usp=sharing

 

4 REPLIES 4
v-rongtiep-msft
Community Support
Community Support

Hi @JFG123 ,

 I have made changes to my answer, please check.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

v-rongtiep-msft
Community Support
Community Support

Hi @JFG123 ,

Please have a try.

Create two tables  with the related columns of data.

 

Car Type = VALUES('Test Data'[Car Type])
Colour = VALUES('Test Data'[Colour])

 

Create a measure.

 

maxdata = 
var _scar = maxx(FILTER(ALLEXCEPT('Test Data','Test Data'[Test Type]),[Car Type]=SELECTEDVALUE('Car Type'[Car Type])),[Test Value])
var _scol = MAXX(FILTER(ALLEXCEPT('Test Data','Test Data'[Test Type]),[Colour]=SELECTEDVALUE('Colour'[Colour])),[Test Value])
var _sboth = maxx(FILTER(ALLEXCEPT('Test Data','Test Data'[Test Type]),[Colour]=SELECTEDVALUE('Colour'[Colour]) &&[Car Type]=SELECTEDVALUE('Car Type'[Car Type])),[Test Value])
var _sno = MAXX(ALLEXCEPT('Test Data','Test Data'[Test Type]),[Test Value])
return
SWITCH(TRUE(),
SELECTEDVALUE('Car Type'[Car Type])=BLANK() &&SELECTEDVALUE(Colour[Colour])=BLANK(),_sno,
SELECTEDVALUE('Car Type'[Car Type])=BLANK() &&SELECTEDVALUE(Colour[Colour])<>BLANK(),_scol,
SELECTEDVALUE('Car Type'[Car Type])<>BLANK() &&SELECTEDVALUE(Colour[Colour])=BLANK(),_scar,
_sboth)

 

 

 

 

11.PNG22.PNG

 

 

If I misunderstood your meaning, please provide your desired output.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

@JFG123 , Try if this can help

calculate(Max(Table[Test Value]), allselected())

 

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

I have included a sample Pbix file.

 

The provided soln does not assist, it does not account for;

1. if different names are selected the value changes to reflect the data in the table

2. Doesn't account for the different test type grouping structure

 

I have added the below image to assist with the interpretation (based on simple sample data). In the initial portion the select for car type is yarris and colour is all. The comparison value is calculated by test type for the data in the table, with no filter on name.

 

The second portion, shows what should happen after filtering for just JOHN, with the same filters as before, the comparison value shouldnt change just because the name has been filtered down.

 

JFG123_0-1639626640738.png

 

 

 

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.