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

[houba139]

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.

[Anonymous · Accepted solution]

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.

[houba139]

Thank you, it works!