Forum Discussion

BKnecht's avatar
BKnecht
Kudo Kingpin
10 years ago
Solved

DAX Calculate IF OR Statement

Im pretty new to writing DAX, and am trying to do something that would be simple in excel, but I can't seem to create it in Power BI. 

 

I'm trying to create a custom measure that says:

 

Calculate the Sum of Column X IF Column A = "Renewal" OR Column B = "Needs Alignment".

 

Very simple measure, but I can't seem to get the OR statement in there, and I can't use the filters in the Calculate function because once I put "Column X = "Renewal", it filters out all of the "Column B = "Needs Alignment". 

 

Any ideas? Any help would be greatly appreciated. 

  • Anonymous's avatar
    Anonymous
    10 years ago

    CALCULATE(

    SUM(Table[Column X]),

    FILTER(

    Table,

    Table[Column A] = "Renewal" ||

    Table[Column B] = "Needs Alignment")

    )

17 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    CALCULATE(

    SUM(Table[Column X]),

    FILTER(

    Table,

    Table[Column A] = "Renewal" ||

    Table[Column B] = "Needs Alignment")

    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Lets say both values "renewal" and "needs assignment" are in column A,
      is there a way to mention both values in the filter (or anywhere else) without haviing the mention Table [column a] twice?

      so instead of:

      Table[Column A] = "Renewal" ||

      Table[Column A] = "Needs Alignment"

       

      Something like:

      Table[Column A] = ("Renewal" || "Needs Alignment")

       

      tried creating a VAR to return into my logical function but I dont get it right somehow

       

      Any ideas? Anonymous 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous  Table[Column A] IN {"Renewal", "Needs Assignment"}

    • BKnecht's avatar
      BKnecht
      Kudo Kingpin
      10 years ago

      That was so much easier than I was trying to make it, didn't even know about the Filter function, this worked perfectly, Thank you!

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        CALCULATE(<expression>, FILTER( <table>, <table[column] = condition>)) is probably the most generally useful pattern to learn in DAX. The vast majority of measures I write either follow this pattern or contain a part that follows this pattern.

         

         

  • Google5ive's avatar
    Google5ive
    Frequent Visitor
    4 years ago

    What if you have a multiples tables that need produce a Sum only if they have a value above 0 for example and I know this code isnt correct but im trying to explain

     

    Calculate( Sum(
    if Column 1 > 0,

    if Column 2 > 0,

    if Column 3 > 0)

     

     

    How would I write something in the proper format in DAX

     

    since i cant use if statements

     

    This is what im trying to convert into DAX

     

    SUM(iif((Fields.ADDTL_INST > 0
    Or Fields.ADDTL_REF > 0
    Or Fields.EXCISE_TAX > 0
    Or Fields.RECORDING_FEES > 0
    Or Fields.NON_STANDARD_FEES > 0)