topic DAX Formula if else over two different tables in DAX Commands and Tips

DAX Formula if else over two different tables

Anonymous — Fri, 20 May 2022 10:40:24 GMT

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 =

(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.

Re: DAX Formula if else over two different tables

daXtreme — Fri, 20 May 2022 17:43:29 GMT

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.

Re: DAX Formula if else over two different tables

v-xiaotang — Tue, 24 May 2022 08:41:16 GMT

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.