Pull column from variable table in DAX/ Lookup in DAX

[powerpuffgurls]

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

[FreemanZ]

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: