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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
wheelsshark
Frequent Visitor

DAX function

Hi 

I have a datatable like

wheelsshark_0-1679541042763.png

 

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

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
Super User

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

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

View solution in original post

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
wheelsshark
Frequent Visitor

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

 

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
wdx223_Daniel
Super User
Super User

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors