Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ccyangdi
Employee
Employee

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

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?

 

Capture.JPG

2 REPLIES 2
SteveCampbell
Memorable Member
Memorable Member

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  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

 

 



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Capture1.JPG

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.