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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
brettgrubbs
Frequent Visitor

Matrix cell markers complex logic for display works except on 2 of my rows.

This of course may not be the best way to do this but this is what the CEO wants to mimic an excel KPI spreadsheet. I have created the Matrix and linked it to a KPI table then I have some measures that calculate the values needed for each month (they use different data and some are percentages and others are whole number days. the first measure below is the cell icon marker logic and the second is the measure I am using to display the correct info in the matrix. It all works great except for when the KPI_ID is 7 or 8. When it is one of these two rows the "Goal Marker" calculates 0 for the value even though the "KPI_Display_Measure" displays the correct values so it doesn't show the correct cell marker. See image below code.  

 

KPI_Display_Measure = VAR vDisplayRow = SELECTEDVALUE(KPI_Table[KPI_Id])

RETURN  SWITCH (True(),AND(vDisplayRow >=7,vDisplayRow <=14),FORMAT([Percent Total AR <90],"##%"),
                vDisplayRow = 17, FORMAT([Percent Total Credit Balance],"##%"),
                vDisplayRow = 6, FORMAT([Days in AR], "####"),
                AND(vDisplayRow >=1,vDisplayRow <=5),"" //placeholder I do not have these KPIs built yet
                )
Goal Marker TEST = VAR vGoal = SELECTEDVALUE(KPI_Table[Goal])
              VAR vDisplayRow = SELECTEDVALUE(KPI_Table[KPI_Id])
              VAR vInverse = IF(vDisplayRow = 17 || vDisplayRow = 6 || vDisplayRow = 5 || vDisplayRow = 16,1,0)            
              VAR vGoalYellow = IF(vInverse = 0, vGoal - (vGoal*.05), vGoal + (vGoal*.05))
              VAR vValue = SWITCH (True(),
                                        AND(vDisplayRow >= 9,vDisplayRow <=14),ROUND([Percent Total AR <90],2),
                                        vDisplayRow = 7,ROUND([Percent Total AR <90],2),
                                        vDisplayRow = 8,ROUND([Percent Total AR <90],2),
                                        vDisplayRow = 17, ROUND([Percent Total Credit Balance],2),
                                        vDisplayRow = 6, ROUND([Days in AR],0) 
                                      )
              

RETURN /* IF(vInverse = 1,
                       IF(vValue <= vGoal,"CircleHigh", IF(vValue >= vGoalYellow, "SignLow","SignMedium"))
                      ,IF(vValue >= vGoal,"CircleHigh", IF(vValue <= vGoalYellow, "SignLow","SignMedium")))  */
vGoal &" - "&  vGoalYellow &" - "& vValue  &" - "&vDisplayRow//  vPerctAR &" - "& vValueCredit

 

Untitled.png

 

1 ACCEPTED SOLUTION

MAX didn't change the outcome but I finally figured out the error... It was ultimately due to a realtionship to the KPI_ID but it was further upstream in one of the measures. I ended up having to change a few measures and rework my source SQL because those two rows would never have worked because they were coming from an table that could not be related to the KPI_Name.  

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@brettgrubbs Difficult to know what exactly is going wrong there. You might try using MAX instead of SELECTEDVALUE. SELECTEDVALUE returns blank by default if there are more than one values in context.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

MAX didn't change the outcome but I finally figured out the error... It was ultimately due to a realtionship to the KPI_ID but it was further upstream in one of the measures. I ended up having to change a few measures and rework my source SQL because those two rows would never have worked because they were coming from an table that could not be related to the KPI_Name.  

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.