Forum Discussion

houba139's avatar
houba139
Frequent Visitor
3 years ago
Solved

Actual (DirectQuery) vs. Target1 and Target2 (Import)

Hi, can anybody help with dax definition?

 

I'm stuck with connecting targets and actuals. The goal is to conditionally format actual values as per target values.

 

 

 

 

1:1 relationship

 

Is this even achievable, meaning combining data from DQ and Import mode? 

 

 

Thank you.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi houba139 

    You can refer to the following example

    1.You can create a measure

    Measure = var a=CALCULATE(SUM('Table (2)'[Target1]),FILTER('Table (2)',[SensorID]=MAX('Table'[SensorID])))
var b=CALCULATE(SUM('Table (2)'[Target2]),FILTER('Table (2)',[SensorID]=MAX('Table'[SensorID])))
return SWITCH(TRUE(),MAX('Table'[Value])<a,-1,MAX('Table'[Value])>=a&&MAX('Table'[Value])<b,0,MAX('Table'[Value])>=b,1)

    Output

     

    2.You can create a calculated table

    Table 2 = ADDCOLUMNS('Table',"Judgement",SWITCH(TRUE(),'Table'[Value]<CALCULATE(SUM('Table (2)'[Target1]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID]))),-1,'Table'[Value]>=CALCULATE(SUM('Table (2)'[Target1]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID])))&&'Table'[Value]<CALCULATE(SUM('Table (2)'[Target2]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID]))),0,'Table'[Value]>=CALCULATE(SUM('Table (2)'[Target2]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID]))),1))

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi houba139 

    You can refer to the following example

    1.You can create a measure

    Measure = var a=CALCULATE(SUM('Table (2)'[Target1]),FILTER('Table (2)',[SensorID]=MAX('Table'[SensorID])))
var b=CALCULATE(SUM('Table (2)'[Target2]),FILTER('Table (2)',[SensorID]=MAX('Table'[SensorID])))
return SWITCH(TRUE(),MAX('Table'[Value])<a,-1,MAX('Table'[Value])>=a&&MAX('Table'[Value])<b,0,MAX('Table'[Value])>=b,1)

    Output

     

    2.You can create a calculated table

    Table 2 = ADDCOLUMNS('Table',"Judgement",SWITCH(TRUE(),'Table'[Value]<CALCULATE(SUM('Table (2)'[Target1]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID]))),-1,'Table'[Value]>=CALCULATE(SUM('Table (2)'[Target1]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID])))&&'Table'[Value]<CALCULATE(SUM('Table (2)'[Target2]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID]))),0,'Table'[Value]>=CALCULATE(SUM('Table (2)'[Target2]),FILTER('Table (2)',[SensorID]=EARLIER('Table'[SensorID]))),1))

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.