cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Retrieve a text value in one column based on MAX in another column

Hi,

 

I have the following table. There are 8 machine ID's. What I need is a table which shows the color of the maximum last status date/time. So for machine 1 this is the color red, for machine 2 color red, machine 3 color orange...etc. I tried selected value and lookup value together with maximum last status date / time, but don't get the right solution. Please advise! Thanks in advance.

 

Power BI.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Retrieve a text value in one column based on MAX in another column

Hi @Hyperchef1969 ,

 

Please use below measure: 

Measure = var a  = CALCULATE(MAX('Table'[LastStatus]),ALLEXCEPT('Table','Table'[Machine ID]))
Return
IF(MAX('Table'[LastStatus])=a,1,0)

Then filter the measure = 1 :

02.PNG

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
adetogni Established Member
Established Member

Re: Retrieve a text value in one column based on MAX in another column

Without showing you the code, proceed one step at a time (in a calc column)

- first FILTER out your rows (using the machineid) -> now you have your 4 rows

- use TOPN to retrieve the top 1 row based on last status -> now you have 1 row

- use SELECTCOLUMNS to extract the column you need -> now you have 1 row and 1 column

 

so in pseudo code is

status=SELECTCOLUMNS(TOPN(1,FILTER(yourfilter on machine id),Laststatus),"any name",yourtable[machine_status])

adetogni Established Member
Established Member

Re: Retrieve a text value in one column based on MAX in another column

to be very honest, this is the solution I use in similar cases. I understand is convoluted and maybe there are better solution, so if anyone has better ideas, I am looking forward to.

 

One important caveat: TOPN returns multiple rows if there is a tie (i.e. if there are more than one row with the same "top" last date). So it would be better to protect yourself from future occurences of this situation by either including in another TOPN or using SUMMARIZE or other functions that returns just one row.

Re: Retrieve a text value in one column based on MAX in another column

Hi,

 

Filtered on machine ID so that I have 4 rows. After that I can't implement the TopN into the visual (because it is a date?). Formula does not work. Can you be more specific to the solution? Thanks in advance!!

adetogni Established Member
Established Member

Re: Retrieve a text value in one column based on MAX in another column

Add a calculated column in your table that puts a "1" for the max date rows (i.e. the ones you want to show). 
Then add your data in a talbe and filter the table where your calculated column is 1

Re: Retrieve a text value in one column based on MAX in another column

Hi,

 

Tried following column based calculation (working on direct query mode)

Max_end_time = IF('Machine Data Uptime'[End time]=MAX('Machine Data Uptime'[End time]),1,0)

Then I get the message 'Function 'MAX' is not allowed as part of a calculated column DAX expressions on Direct Query models. Is there an alternative?

 

 
Highlighted
Community Support Team
Community Support Team

Re: Retrieve a text value in one column based on MAX in another column

Hi @Hyperchef1969 ,

 

Please use below measure: 

Measure = var a  = CALCULATE(MAX('Table'[LastStatus]),ALLEXCEPT('Table','Table'[Machine ID]))
Return
IF(MAX('Table'[LastStatus])=a,1,0)

Then filter the measure = 1 :

02.PNG

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 354 members 3,362 guests
Please welcome our newest community members: