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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BastiaanBrak
Helper IV
Helper IV

DAX query: LOOKUPVALUE with Slicers

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:
VLOOKUP1.PNG

 

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).

VLOOKUP2.PNG


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

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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,

 

DAX1.jpg

 

DAX2.jpg

 

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.

 

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

v-zhenbw-msft
Community Support
Community Support

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,

 

DAX1.jpg

 

DAX2.jpg

 

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.

Max value location =

var max_value = CALCULATE([Max value],ALLSELECTED())
var first_location = CALCULATE(MIN('Table'[Location]),FILTER('Table','Table'[Data_Value]=max_value))
var last_location = CALCULATE(MAX('Table'[Location]),FILTER('Table','Table'[Data_Value]=max_value))

return

IF(first_location = last_location,CALCULATE(MAX('Table'[Location]),FILTER('Table','Table'[Data_Value]=max_value)),"Multiple locations")





 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.