Forum Discussion

Levii's avatar
Levii
New Member
2 years ago

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

 IFSELECTEDVALUE('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 */

    IFSELECTEDVALUE('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

  • You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.