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

[Hyperchef1969]

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.

[Anonymous]

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])

[Anonymous]

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.

[Hyperchef1969]

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!!

[Anonymous]

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

[Hyperchef1969]

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?

[v-diye-msft · Accepted solution]

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 :