Measure Value based on conditional column from another table

ThuJa23 — Thu, 23 Feb 2023 07:14:00 GMT

Hi all,

I have 2 tables in a multidimension cube (that is why here I can use only "Create Measure" and cannot use "Create Column" for the calcualtion)

1. Table 1: where have information of KST Original, PNr, Sollz, Istz, Effzeit. The filter for this table that value should be filtered when KSTWech is "Ja".

2. Table 2: where have information of KST actual, PNr, SollGesamt, HauptKST. Here should not be affected by Filter KSTWech.

HauptKST Measure: Here we would like to take only values in some selected "KST actual" IN {"95130","95140","95150","95160","95220","95250","95270"}
SollGesamt Measure: Here we would like to sum all values from all "KST actual"

I would like to have merge values that taking information of SollGesamt & HauptKST from Table 2 into Table 1

I created measure

SollGesamt = CALCULATE([Sollz], ALL(FACT_ProductivityList[KSTWech]))

HauptKST = CALCULATE([Sollz],ALL(FACT_ProductivityList[KSTWech]), Kostenstelle[OrgNr] IN {"95130","95140","95150","95160","95220","95250","95270"})

But, when I display these 2 measures in Table 1, the values of HauptKST is not right follow the condition of KST Original

For ex:

Table 2, PNr 669 has KST actual on 95140 and N.A with Sollgesamt is 160 and selected KST 95140 with HauptKST values is 152

Table 1, PNr 669 has KST Ori just only 95160, so the values of HauptKST here should be 0

The final table 1 should be like this

but, it show the HauptKST value inTable 1 also of 152 (from 95140, NOT 95160).

I do not know how to adjust my measure to let it display HauptKST value from table 2 (also from some selected KST actual as avove) rightly with condition column of KST Ori of table 1

Could you please help me in this case?

Thanks so much

Thu

topic Measure Value based on conditional column from another table in DAX Commands and Tips

Measure Value based on conditional column from another table