Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
@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.
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.
User | Count |
---|---|
106 | |
89 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |