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
powerpuffgurls
Regular Visitor

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
1 REPLY 1
FreemanZ
Super User
Super User

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:

FreemanZ_0-1680225180441.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.