Need DAX correction

[PowerBIBeginer]

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]))))

[Anonymous · Accepted solution]

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.

[PowerBIBeginer]

Thanks for the reply v-yilong-msft 🙂 This works..

[PowerBIBeginer]

can anyone please suggest here.. this is something scalar value error

[PijushRoy]

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]

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.