DAX Formula if else over two different tables

[Anonymous]

Hi Community, 

I hope you can support me.

Goal:

I would like to create a matrix visualization that shows the "Elemente" distributed across the "Filiale". 

Problem:

The problem is that the intermediate table does not contain all values. I would like to map the following:

Show the "Elemente" from table "VC002_Order_Info" per "Filiale"--> If from table "T034_Order" the field "T034_C_Employee" is empty, take the "Filiale" from table "Personal Organigramm (2)", and if the field "T034_C_Employee" is filled, take the "Filiale" from table "Personal Organigramm".

Table Links:
T034_C_Employee with "Name" from table "Personal Organigramm"
T034_Customer_ID with T034_Customer_ID from table "Personal Organigramm (2)"

I tried it with the follow DAX formula:
FilialeDynamisch = 

IF

(CALCULATE(

     SUM('VC002_ORDER_INFO'[Elemente]),

     FILTER(T034_ORDER,T034_ORDER[T034_C_EMPLOYEE] IN {""})

),

CALCULATE(SUM(VC002_ORDER_INFO[Elemente]),FILTER('Personal Organigramm (2)','Personal Organigramm (2)'[Filiale])),

CALCULATE(SUM(VC002_ORDER_INFO[Elemente]),FILTER('Personal Organigramm','Personal Organigramm'[Filiale]))

)

But this is not successfutl.

Thank you very much for your support.

[v-xiaotang]

Hi @Anonymous 

To find T034_C_Employee, since the relationship is not obvious in the picture, please search according to the following principles: use the relationship between table VC002_Order_Info and T034_Order to find T034_C_Employee related with the current row when you do calculations. 

Seriously, could you please share a sample file(you can replace raw data with bogus data), then it will be much faster to deal with this problem, otherwise I can only provide some theoretical help, which will be more time-consuming.

Best Regards,

Community Support Team _Tang

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

[daXtreme · Accepted solution]

I'd suggest that you revise the documentation on the FILTER function first: https://dax.guide/filter. I'd also kindly suggest that you name your tables and columns some decent, pronounceable (this is very important) names. Seriously, this will make your and your peers' lives much easier.