Forum Discussion

wheelsshark's avatar
wheelsshark
Frequent Visitor
3 years ago
Solved

DAX function

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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

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

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

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

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

  • wheelsshark's avatar
    wheelsshark
    Frequent Visitor
    3 years ago

    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

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Community Champion
      3 years ago

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