Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi ,
I am trying to get the min value of one dimension based on selecting another value of that dimension through a common dimension.
e.g. I have simple table with date, car, colour and amount. The car can have multiple colours. Green is the cheapest. I select red in a filter so I can display the cost of red in one visual, but I also want to see the cheapest for comparision.
My approach is get the car first of the selected colour, then get the list of all colours associated with that car, then get try to get the value of those colours however but this is just returning min value of the selected colour.
Solved! Go to Solution.
You can create a measure like
Cheapest colour =
var summaryTable = CALCULATETABLE( SUMMARIZE('Table', 'Table'[Car], 'Table'[Colour], 'Table'[Amount]), REMOVEFILTERS('Table'[Colour]))
var cheapestColours = TOPN(1, summaryTable, [Amount], ASC)
return CONCATENATEX( cheapestColours, [Colour], ", ")
The CONCATENATEX is in case there are multiple colours at the same price
You can create a measure like
Cheapest colour =
var summaryTable = CALCULATETABLE( SUMMARIZE('Table', 'Table'[Car], 'Table'[Colour], 'Table'[Amount]), REMOVEFILTERS('Table'[Colour]))
var cheapestColours = TOPN(1, summaryTable, [Amount], ASC)
return CONCATENATEX( cheapestColours, [Colour], ", ")
The CONCATENATEX is in case there are multiple colours at the same price
thanks @johnt75 that looks like its working as expected...now to apply to my actual complicated dataset!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |