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

[Anonymous]

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.

[amitchandak]

@Anonymous , use this with machine id in a visual

lastnonblankvalue(table[laststatus], max(table[machinestatus]))

[Greg_Deckler]

@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 

[Anonymous]

@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

[Anonymous]

@Greg_Deckler sorry that was bad english, i mean in a graph i just want to see how many of each level there are

[Greg_Deckler]

@Anonymous - OK, maybe this:

Measure = 
  VAR __Color = MAX('Table'[Machine_status])
  VAR __Table = SUMMARIZE('Table',[Machine ID],"__Last",MAX([Laststatus]))
  VAR __Table1 = ADDCOLUMNS(__Table,"__Status",MAXX(FILTER('Table','Table'[Machine ID]=[Machine ID] && 'Table'[Laststatus]=[__Last])
RETURN
  COUNTROWS(FILTER(__Table1,[__Status] = __Color))

[Anonymous]

@Greg_Deckler thank you again, i am getting the follwoing error;

not sure if i am missing a ) or something but when it hits RETURN its giving me the red underlining. To add this to a bar chart would i be able to just use the axis as machine status then this measure as the count?

[Greg_Deckler]

@Anonymous - Yeah, there is a missing ) and other issues. This is what happens when I code without testing. Which is why it is super helpful to post sample data as text instead of images...

Measure = 
  VAR __Color = MAX('Table'[Machine_status])
  VAR __Table = SUMMARIZE('Table',[Machine ID],"__Last",MAX([Laststatus]))
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "__Status",
        MAXX(
          FILTER('Table','Table'[Machine ID]=[Machine ID] && 'Table'[Laststatus]=[__Last]),
          [Status]
        )
     )
RETURN
  COUNTROWS(FILTER(__Table1,[__Status] = __Color))

[Anonymous]

@Greg_Deckler again thank you for the support, when i put the machine status into the axis then this into value i get the following;

[Greg_Deckler]

@Anonymous - Hard to tell but seems like somewhere along the line you are trying to compare Laststatus and Machine_status perhaps. Let me double-check. Can you post some sample data as text so that I can copy and paste it into a PBIX file and actually do the calculation? Then I can upload the PBIX.

Measure = 
  VAR __Color = MAX('Table'[Machine_status])
  VAR __Table = SUMMARIZE('Table',[Machine ID],"__Last",MAX([Laststatus]))
  VAR __Table1 = 
    ADDCOLUMNS(
      __Table,
      "__Status",
        MAXX(
          FILTER('Table','Table'[Machine ID]=[Machine ID] && 'Table'[Laststatus]=[__Last]),
          [Machine_status]
        )
     )
RETURN
  COUNTROWS(FILTER(__Table1,[__Status] = __Color))

[Anonymous]

thank you @Greg_Deckler the laststaus will also be a filter on the page so if i filtered to 1 date it will be th elatest for that date selected , please see below sample data;

Machine Id	Laststatus	Machine Status
1	08/01/2020	Error
1	09/01/2020	Green
1	10/01/2020	Orange
1	11/01/2020	Red
2	08/01/2020	Error
2	09/01/2020	Green
2	10/01/2020	Orange
2	11/01/2020	Red
3	08/01/2020	Error
3	09/01/2020	Green
3	10/01/2020	Orange
3	11/01/2020	Green
4	08/01/2020	Green
4	09/01/2020	Orange
4	10/01/2020	Red
4	11/01/2020	Error
6	08/01/2020	Red
6	09/01/2020	Orange
6	10/01/2020	Green
6	11/01/2020	Orange

[Greg_Deckler]

@Anonymous  - OK, sooooo much easier to debug. PBIX is attached, you want Table (24), Measure 24.

Measure 24 = 
  VAR __Color = MAX('Table (24)'[Machine Status])
  VAR __Table = SELECTCOLUMNS(SUMMARIZE(ALL('Table (24)'),[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))

[Anonymous]

@Greg_Deckler  this does work but doesnt when i try filter dates, so if i filter using the laststatus this doesnt show anything in the table i would expect the measure to recalculate to say this is now the latest date it could be

[Greg_Deckler · Accepted solution]

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

[Greg_Deckler]

@Anonymous - Suspense is killing me, did we finally get to the answer?!?!??

[Anonymous]

@Greg_Deckler it did work but had the issue of when putting into a table it was showing duplicate levels for some machine IDs which it hsould be showing the latest but i managed to do it via;

Machine Count by Last Status =
VAR MaxDateTimePerMachine =
    ADDCOLUMNS (
        VALUES ( YourTable[Machine ID] ),
        "@MaxDateTime",
            CALCULATE (
                MAX ( YourTable[Laststatus] ),
                // Remove status filter because status is on chart axis
                REMOVEFILTERS ( YourTable[Machine_status] )
            )
    )
VAR MaxDateTimePerMachineLineage =
    TREATAS ( MaxDateTimePerMachine, YourTable[Machine ID], YourTable[Laststatus] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( YourTable[Machine ID] ),
        MaxDateTimePerMachineLineage
    )

[Greg_Deckler]

@Anonymous - Glad we were able to get there in the end!

[Anonymous]

@Greg_Deckler thank you for all your help as always