Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi all,
I'm stuck on a particular DAX issue and hoping to get some advice here.
I've got a datatable with following fields: Region, Location, Date, Category, Value and would like to find the Location of a particular maximum (or minimum) Value depending on the Slicers present in a report that may be applied to Region, Date and Category. I have worked out a measure to find the maximum value ('Max Value') so now the challenge is to use LOOKUPVALUE to find the corresponding Location for that value.
Currently I'm using following:
Location of Max Value =
LOOKUPVALUE(
'Table'[Location],
'Table'[Value],
'Table'[Max Value],
"Multiple locations"
)
As per screenshot below, it appears to do the job, correctly finding that Location of Max Value is London:
However, the code finds multiple locations when only Category B is selected; the Location should be London (again) but the current DAX formula is looking at ALL the data (i.e. it isn’t taking into account the Slicer active on Category) and therefore finds multiple locations because Birmingham also has a value of 33 (albeit in a different category).
How should I adapt the 2nd expression in my DAX measure below?
Location of Max Value =
LOOKUPVALUE(
'Table'[Location],
'Table'[Value],
'Table'[Max Value],
"Multiple locations"
)
Many thanks, Bastiaan
Solved! Go to Solution.
Hi @BastiaanBrak ,
The lookupvalue function is filtered down to zero value or an error when more than one distinct value.
You can refer this article.
And we can use the following measures to meet your requirement.
1. Create a max value measure.
Max_value = MAX('Table'[value])
2. Then create a Location of Max Value measure.
Location of Max Value =
var max_value = CALCULATE([Max_value],ALLSELECTED())
return
CALCULATE(MAX('Table'[Location]),FILTER('Table','Table'[value]=max_value))
The result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share the link from where i can download your PBI file.
Hi @Ashish_Mathur, thanks for your reply, the solution offered by @v-zhenbw-msft worked well for me but if you are still interested I can upload the test workbook. Just let me know.
Best wishes, Bastiaan
Hi @BastiaanBrak ,
The lookupvalue function is filtered down to zero value or an error when more than one distinct value.
You can refer this article.
And we can use the following measures to meet your requirement.
1. Create a max value measure.
Max_value = MAX('Table'[value])
2. Then create a Location of Max Value measure.
Location of Max Value =
var max_value = CALCULATE([Max_value],ALLSELECTED())
return
CALCULATE(MAX('Table'[Location]),FILTER('Table','Table'[value]=max_value))
The result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Many thanks @v-zhenbw-msft, I modified your solution a little bit to enable indicating if there are multiple locations for a given maximum value, since your solution using MAX('Table'[Location]) is returning the last one (in alphabetical order) if there are more than one.
Hi @BastiaanBrak ,
Very happy our reply is helpful to you.😊
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |