Forum Discussion
Calculation for Overall State
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
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 )
1 Reply
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 )