Forum Discussion

PowerBIBeginer's avatar
PowerBIBeginer
Helper V
2 years ago
Solved

Need DAX correction

Hi All,

 

I need dax help /correction here.. Can anyone pls help here..

 

test dax = 
IF(AND(
FILTER('SR','SR'[Complex]="LOST"),
FILTER('SR',CONTAINSSTRING('SR'[Holder],"TPA"))),
CALCULATE(COUNT('SR'[ID])),
IF(FILTER('SR',Not(CONTAINSSTRING('SR'[Holder],"TPA"))),
CALCULATE(COUNT('SR'[ID]))))
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PowerBIBeginer ,

    First, let's look at nesting of IF functions. You have two IF functions in your expression, but there is no logical connection between them. This can result in calculations that are not as expected. You need to explicitly specify the relationship between the IF functions, for example, by using the ELSE keyword.

    Second, you use the AND function in the first IF function, but you do not specify the relationship between the first and second conditions. You need to separate the two conditions with a comma, or use another logical operator (such as &&) to connect them.

    Depending on your needs, you can try the following corrections:

    Corrected DAX = 
VAR IsLost = 'SR'[Complex] = "LOST"
VAR ContainsTPA = CONTAINSSTRING('SR'[Holder], "TPA")

RETURN
IF(
    IsLost && ContainsTPA,
    CALCULATE(COUNT('SR'[ID])),
    IF(
        NOT ContainsTPA,
        CALCULATE(COUNT('SR'[ID]))
    )
)

     

     

     

    Best Regards

    Yilong Zhou

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PowerBIBeginer ,

    First, let's look at nesting of IF functions. You have two IF functions in your expression, but there is no logical connection between them. This can result in calculations that are not as expected. You need to explicitly specify the relationship between the IF functions, for example, by using the ELSE keyword.

    Second, you use the AND function in the first IF function, but you do not specify the relationship between the first and second conditions. You need to separate the two conditions with a comma, or use another logical operator (such as &&) to connect them.

    Depending on your needs, you can try the following corrections:

    Corrected DAX = 
VAR IsLost = 'SR'[Complex] = "LOST"
VAR ContainsTPA = CONTAINSSTRING('SR'[Holder], "TPA")

RETURN
IF(
    IsLost && ContainsTPA,
    CALCULATE(COUNT('SR'[ID])),
    IF(
        NOT ContainsTPA,
        CALCULATE(COUNT('SR'[ID]))
    )
)

     

     

     

    Best Regards

    Yilong Zhou

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

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    Hi PowerBIBeginer 

    Please check 

     

    test_dax =
IF (
    AND (
        FILTER ( 'SR', 'SR'[Complex] = "LOST" ),
        FILTER ( 'SR', CONTAINSSTRING ( 'SR'[Holder], "TPA" ) )
    ),
    CALCULATE ( COUNT ( 'SR'[ID] ) ),
    IF (
        FILTER ( 'SR', NOT CONTAINSSTRING ( 'SR'[Holder], "TPA" ) ),
        CALCULATE ( COUNT ( 'SR'[ID] ) )
    )
)

     

     


    Let me know if that works for you


    If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

    Thanks
    Pijush
    Linkedin

    • PowerBIBeginer's avatar
      PowerBIBeginer
      Helper V
      2 years ago

      Hi, Thanks for your reply. But you only made change in design otherwise it is same what I wrote..

       

      The one which I posted, giving me error

      - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.