Forum Discussion
Calling a semaphore measure in a new measure ; switch/ifcode
Hello all,
I have a measure that calculates semaphore value based on Target column and Value column, but it works on single filter selection.
Now i want to create a new measure that will call upon semaphore one and do next logic:
If ,for two selected KPI (semaphore values), one is green and other is yellow or black ,then display green sempahore otherwise red one..
I've tried muliple things but each time it only works for single selection of filter....probably because of SELECTEDVALUE function.
Any help suggestion is welcomed!
Semaphore measure:
Semaphores NEW 2024 YTD =
VAR CurrentMonth = MONTH(TODAY())
VAR JanToCurrentMonthVALUE = CALCULATE(
SUM('Actual_2024'[Value]),
FILTER(
'Actual_2024',
'Actual_2024'[Month] >= 1 && 'Actual_2024'[Month] <= 12
),'Actual_2024'[Month] <= CurrentMonth
)
var notisblank = CALCULATE(COUNTROWS('Actual_2024'),FILTER(Actual_2024,Actual_2024[Month]>= 1 && Actual_2024[Month]<= 12), NOT(ISBLANK('Actual_2024'[Value])))
var JanToCurrentMonth =
DIVIDE(JanToCurrentMonthVALUE,notisblank)
// VAR JanToCurrentMonthTArget = CALCULATE(
// SUM('Targets_2024'[Target]),
// FILTER(
// 'Targets_2024',
// 'Targets_2024'[Month] >= 1 && 'Targets_2024'[Month] <= 12
// ),'Targets_2024'[Month] <= CurrentMonth-1
// )
// var Target =
// DIVIDE(JanToCurrentMonthTArget,notisblank)
var TargetX =CALCULATE(CALCULATE(SUM(Targets_2024[Target]),Targets_2024[Month]= 2024))
VAR FourPercentage = TargetX / 20
VAR FourPercentageAbs = ABS(FourPercentage)
RETURN
IF( SELECTEDVALUE('CpC KPIs_2024'[Semaphore]) = "G" ,
IF(ISBLANK(JanToCurrentMonth) , "⚫" /*"#808080"*/ ,
IF(ISBLANK(TargetX) , "⚫" /*"#808080"*/ ,
IF(JanToCurrentMonth >= TargetX , "🟢" /* "#67BB06" */ , /* green */
IF((JanToCurrentMonth-TargetX)* -1 <= FourPercentage , "🟡" /*"#fffb00"*/ ,/* yellow */
"🔴" /* "#F00202" */ /* red */
)) )),
/* RED ABOVE TARGET */
IF( SELECTEDVALUE('CpC KPIs_2024'[Semaphore]) = "R" ,
IF(ISBLANK(JanToCurrentMonth) , "⚫" /*"#808080"*/ ,
IF(ISBLANK(TargetX) , "⚫" /*"#808080"*/ ,
IF(JanToCurrentMonth <= TargetX , "🟢" /* "#67BB06" */ /* green */ ,
IF((JanToCurrentMonth-TargetX) <= FourPercentage ,"🟡" /* "#fffb00" */ ,/* yellow */
"🔴" /* "#F00202" */ /* red */
)) )),"⚪"))
__________________
1 Reply
- lbendlin
Super User
You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.