topic DAX function in DAX Commands and Tips

DAX function

wheelsshark — Thu, 23 Mar 2023 03:11:22 GMT

Hi

I have a datatable like

in Power BI I have a dax function to find Max of each Town(T1, T2, T3) - it gives (201,200,200). Depending on the slicer selection it shows this value

HighestTotal =

MAXX(

KEEPFILTERS(VALUES('tempdumpdata'[Total])),

CALCULATE(SUM('tempdumpdata'[Total]))

)

And Depending on this selection I write another DAX to diplay the related Town in another card,

LookUpTown = LOOKUPVALUE(

tempdumpdata[Town],

tempdumpdata[Total],

[HighestTotal])

I get error when I try to get the Town for highest value 200, bz, T2 and T3 have highest value as 200. I see error like -

"A table of multiple values was supplied where a single value was expected."

what should i do in this case

Re: DAX function

wdx223_Daniel — Thu, 23 Mar 2023 03:45:31 GMT

if the lookup value is duplicated, lookupvalue will get a error.

LookupTown=VAR _h=[HighestTotal] RETURN CONCATENATEX(FILTER('tempdumpdata','tempdumpdata'[Total]=_h),'tempdumpdata'[Town],",")

Re: DAX function

wheelsshark — Thu, 23 Mar 2023 21:27:48 GMT

Thank you Daniel, it works. But I cant understand.

Highest Total is 200 for C1, while var _h is 200
Return concatenatex(filter('tempdumpdata', 'tempdumpdata'[Total] = 200), 'tempdumpdata[Town] - this gives T3 and T2. I need only T3.

bz the slicer selection is C1. Can you help me understand

Re: DAX function

Anonymous — Fri, 07 Apr 2023 09:41:13 GMT

HI wheelsshark,

Perhaps you can add a new condition to compare with the name field and selection to filter the result.

LookupTown = VAR _h = [HighestTotal] VAR selection = VALUES ( Table[Name] ) RETURN CONCATENATEX ( FILTER ( 'tempdumpdata', 'tempdumpdata'[Total] = _h && [Name] IN selection ), 'tempdumpdata'[Town], "," )

Regards,

Xiaoxin Sheng

Re: DAX function

wdx223_Daniel — Mon, 10 Apr 2023 02:49:50 GMT

the function of LOOKUPVALUE do not consider any filters outsider. it always lookup all the table.