Forum Discussion
Retrieve a text value in one column based on MAX in another
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 - Updated. Updated the PBIX as well, attached.
Measure 24 = VAR __Color = MAX('Table (24)'[Machine Status]) VAR __Laststatus = MAX('Table (24)'[Laststatus]) VAR __Table = SELECTCOLUMNS(SUMMARIZE(FILTER(ALL('Table (24)'),[Laststatus]=__Laststatus),[Machine ID],"__Last",MAX('Table (24)'[Laststatus])),"__Machine",[Machine Id],"__Last",[__Last]) VAR __Table1 = ADDCOLUMNS( __Table, "__Status", MAXX( FILTER('Table (24)','Table (24)'[Machine ID]=[__Machine] && 'Table (24)'[Laststatus]=[__Last]), [Machine Status] ) ) RETURN COUNTROWS(FILTER(__Table1,[__Status] = __Color))
18 Replies
- Greg_Deckler
Community Champion
Anonymous - You could create a measure like the following:
Measure = VAR __Machine = MAX('Table'[Machine ID]) VAR __Date = MAXX(FILTER(ALL('Table'),[Machine ID] = __Machine),[Laststatus]) RETURN MAXX(FILTER(ALL('Table'),[Machine ID] = __Machine && [Laststatus] = __Date),[Machine_status])This is basically Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Greg_Deckler thank you greg, i amtrying to put this into a chart that looks at number of machine IDs per machine status. Issue is i am guessing i can't do this with that? as i need the measur eto be on the axis
- Greg_Deckler
Anonymous - In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
- amitchandak
Super User
Anonymous , use this with machine id in a visual
lastnonblankvalue(table[laststatus], max(table[machinestatus]))
