Is it possible to check whether a value exists in Matrix?

[ccyangdi]

We have a matrix in powerBI report, we want to add an overall status measure for each column. 

overallStatus = "red" for 19.07 if any red exists in column 19.07, else "yellow" if any yellow exists in column 19.07, else "green"

Is it possible to calculte the measure from the matrix directly?

The red, yellow and green are calculated by a status measure based on threshold in the original matrix. Because the threshold for each row is different, I have to hard code the thresholds in the DAX to get the overallStatus. 

We are thinking of moving the thresholds to a table for easier maintainance, then the logic is to calculate whether the status measure is above its red thresholds for any row, then whether it's above yellow thresholds for any row.  Is there any way to do it?

[SteveCampbell]

Could you give a little more information on how this is calculated? It is a little hard to figure out - is each column a measure? Or is this used as a column header in the matrix. Also, if you could say how the measure works, that would help. From how I read it, it sounds like the rows and columns should be switched, but maybe I do not have enough information.

I think using a numeric for red, yellow, green would help. You can still have the words, but create another measure with 1 = red ,yellow = 2, green = 3, this will help with more efficient calculations.

A total would need to iterate across all rows, something like this:

Total = 
MINX(
    ADDCOLUMNS(
        VALUES('Table'[Column1]),"@Measure",[Measure])
    ,[@Measure]
)

Appreciate your Kudos
Connect with me!

Stay up to date on  
Read my blogs on  

[ccyangdi]

HealthV3 =

VAR mitigated = IF(CALCULATE(DISTINCTCOUNT(CleansedInput[deviceId]), FILTER(ALLEXCEPT(CleansedInput, dimDate[Date], dimEnv[Environment], dimOS[installedSphereVersion], dimEnvOS[installedSphereVersion], CleansedInput[tenantId]), AND(AND(CleansedInput[LatestOS1] = 1, CleansedInput[Environment] = "prod"), RELATED('DHD alert'[Mitigated]) == 1))) > 0, 1, 0)

RETURN

IF(mitigated = 0, IF([% AffectedDevice] > CALCULATE(MIN(CleansedInput[UpperThres])), "Red", IF([% AffectedDevice] > CALCULATE(MIN(CleansedInput[LowerThres])), "Yellow", "Green")), "Purple")

This is how the cell data was calculated, the threshold for each row are different.