Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Formula if else over two different tables

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.

  • daXtreme's avatar
    daXtreme
    4 years ago

    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.

2 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.