Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX condition if

Hello,

 

I have many tables in power query that I don't want to merge. I'd like thanks to DAX to do somethong like this :

 

 If ('Tab1'[Type] = "Pipe")

then return 'Tab1'[thickness]  * Tab2[surface] where 'Tab1'[Size] = 'Tab2'[Size]

else return 'Tab1'[thickness]

 

Tab1
TypeSizethickness
Pipe730
Valve220
Pipe620
Valve5

20

 

Tab2
Sizenew_size
24
59
68
71


I don't know how to manage the "where 'Tab1'[Size] = 'Tab2'[Size]" part of it. Any idea ?

 

Thanks a lot !

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Assuming that you have a relationship between Data Table and Size Table.

     

     

     

     

    Using MEASURES

     

    Thickness*Size =

    SWITCH(
    TRUE(),
    MAX(Data[Type]) = "Pipe" && MAX(Data[Size]) = MAX(Size[Size]), MAX(Data[thickness]) * MAx(Size[new_size]),
    MAx(Data[thickness])
    )

     

     

     

     

     

    USING A CALCULATED COLUMN

     

    Thickness*Size Column =

    SWITCH(
    TRUE(),
    Data[Type] = "Pipe" && Data[Size] = RELATED(Size[Size]), Data[thickness] * RELATED (Size[new_size]),
    Data[thickness]
    )
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Assuming that you have a relationship between Data Table and Size Table.

     

     

     

     

    Using MEASURES

     

    Thickness*Size =

    SWITCH(
    TRUE(),
    MAX(Data[Type]) = "Pipe" && MAX(Data[Size]) = MAX(Size[Size]), MAX(Data[thickness]) * MAx(Size[new_size]),
    MAx(Data[thickness])
    )

     

     

     

     

     

    USING A CALCULATED COLUMN

     

    Thickness*Size Column =

    SWITCH(
    TRUE(),
    Data[Type] = "Pipe" && Data[Size] = RELATED(Size[Size]), Data[thickness] * RELATED (Size[new_size]),
    Data[thickness]
    )
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Spoiler
      Hi,

      Thanks a lot ! the calculated column method works perfectly!
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous ,

         

        Hahaha..

         

        Loved the spoiler.

         

        Happy to help.

         

        Regards,

        Harsh Nathani

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    Can you provide an example based on the data, with the expected result?

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
       

      Hi,

       

      Thanks for answering me. I have the 2 below tables : 

       

      T_Data:

      T_Data

       

      T_size:

      T_size

      I try to do some thing like this : mesure = if('T_Data'[Type] = "Pipe",if('T_Data'[DN] = 'T_size'[DN],SUM('T_Data'[Thickness]) * SUM('T_size'[Surface pipe (m2/ml)]),0),0).

       

      I'd like to have a meusure where I have sum of all pipe thickness by surface  : 'T_Data'[Thickness] * 'T_size'[Surface pipe (m2/ml)] where type = pipe for each DN corresponding surface.

       

      Is that clear or do you need more details ?

       

      Thanks !