Forum Discussion

powerpuffgurls's avatar
powerpuffgurls
Regular Visitor
3 years ago

Pull column from variable table in DAX/ Lookup in DAX

Hi everyone,

 

i have created a variable tabel inside a measure and need to pull column1 value based on maxium value in column 2. However i can't seem to find a  function that will pull  or look up a value from a variable table as  the table does not really exists. it is only  summarized inside the measure. 

 

Basically  in the formula below, i need to  pull the DOW value  where [count] is maximum

 

Day With highest number of calls =

Var _table = SUMMARIZE(LCPhoneData,LCPhoneData[DOW],"Count",COUNTROWS(LCPhoneData))
Var maxcallday = CALCULATE(sumx(_table,[DOW]), FILTER(_table,max([Count])))
return
maxcallday
Need Help

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi powerpuffgurls 

    not sure if i fully get you. 

    Supposing your table like this:

    DOW
    1/1/2023
    1/2/2023
    1/2/2023
    1/3/2023
    1/3/2023
    1/3/2023

     

    then try to plot a measure like this:

     

    Measure = 
VAR _table =
ADDCOLUMNS(
    VALUES(TableName[DOW]),
    "Count",
    CALCULATE(COUNTROWS(TableName))
)
VAR _countmax = MAXX(_table, [Count])
RETURN
MINX(
    FILTER(_table, [Count] = _countmax),
    [DOW]
)

     

    it worked like: