Calculation for Overall State

[Sarumahay]

Hi All,

Pretty new in Power BI. I would like to display overall state for the following below. I have the table below.

Assets ID	Components	Condition Code	Severity Level
UC003	UC003 DRIVE PULLEY ANTIRUNBACK	MINOR	2
UC003	UC003 TRIPPER CST GEARBOX NO.1	NORMAL	1
UC003	UC003 TRIPPER CST GEARBOX NO.1	MINOR	2
UC003	UC003 TRIPPER CST GEARBOX NO.2	NORMAL	1
UC003	UC003 PULLEY, LTU MOBILE OUTBYE	NORMAL	1
UC003	UC003 PULLEY, LTU MOBILE LOWER	ALERT	3
DL009	DL009 ENGINE	NORMAL	1
DL009	DL009 ENGINE	NORMAL	1
DL009	DL009 HYDRAULIC SYSTEM	NORMAL	1
DL009	DL009 TRANSMISSION	NORMAL	1
CM012	CM012 CUTTER HEAD GEARBOX	MINOR	2
CM012	CM012 HYDRAULIC SYSTEM	NORMAL	1
CM012	CM012 LH TRACTION REDUCER	NORMAL	1
CM012	CM012 RH TRACTION REDUCER	NORMAL	1
BR902	BR902 HYDRAULIC SYSTEM	MINOR	2
BR902	BR902 LH TRACTION GEARCASE	ALARM	4
BR902	BR902 RH TRACTION GEARCASE	ALARM	4

I would like to create an Overall State against Asset ID and the output will be the highest priority in this case either using the Condition code column or Severity Level column. The Highest will be ALARM followed by ALERT, MINOR, NORMAL.

Expecting output will be like this one below

Assets ID	Overall State
UC003	ALERT
DL009	NORMAL
CM012	MINOR
BR902	ALARM

Thanks you very much

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached file.

It is for creating a measure.

INDEX function (DAX) - DAX | Microsoft Learn

Overall state measure: =
VAR _maxlevel =
    INDEX (
        1,
        ALL ( Data[Condition Code], Data[Severity Level], Data[Assets ID] ),
        ORDERBY ( Data[Severity Level], DESC ),
        ,
        PARTITIONBY ( Data[Assets ID] )
    )
RETURN
    CALCULATE ( SELECTEDVALUE ( Data[Condition Code] ), _maxlevel )