Use of multiple variables in the same measure

[kkanda]

Hi All,

I have gone through the some related posts in this Forum and SQL BI and I wrote the following DAX expression using multiple variables for the measure:

Test_Measure =

Var Res_2 = FILTER('M1 Notifications', ('M1 Notifications'[Created on] < (TODAY()-1825) && 'M1 Notifications'[Created on] > (TODAY()-365))

Var Res_1 = FILTER(Res_2, ('M1 Notifications'[Notification System Status] = "Outstanding notification" || 'M1 Notifications'[Notification System Status] = "Order assigned" || 'M1 Notifications'[Notification System Status] = "Notification in process"))

RETURN

IF(COUNTROWS(Res_1) <> BLANK(),COUNTROWS(Res_1),UNICHAR((32))

)

I used two Variables to filter the same table in succession for different criteria. This is basically an AND filter.

I get a syntax error for Var Res_1. I am not sure how we can use both Variables instead of writing a long Filter expression combining all conditions. For Var Res_1, can I use the filtered table Res_2? I tried using table name in its place but it is still the same error.

I will appreciate if the experts explain how the successive filters can be applied with Variables.

Thank you in advance

Krishna

[jdbuchanan71 · Accepted solution]

@kkanda 

Your measure was missing a closing parenthisis in the first Var.

Test Measure =
VAR Res_2 =
    FILTER (
        'M1 Notifications',
         (
            'M1 Notifications'[Created on]
                < ( TODAY () - 1825 )
                && 'M1 Notifications'[Created on]
                    > ( TODAY () - 365 )
        )
    )
VAR Res_1 =
    FILTER (
        Res_2,
         ( 'M1 Notifications'[Notification System Status] = "Outstanding notification"
            || 'M1 Notifications'[Notification System Status] = "Order assigned"
            || 'M1 Notifications'[Notification System Status] = "Notification in process" )
    )
RETURN
    IF ( COUNTROWS ( Res_1 ) <> BLANK (), COUNTROWS ( Res_1 ), UNICHAR ( ( 32 ) ) )

[jdbuchanan71 · Accepted solution]

@kkanda 

Your measure was missing a closing parenthisis in the first Var.

Test Measure =
VAR Res_2 =
    FILTER (
        'M1 Notifications',
         (
            'M1 Notifications'[Created on]
                < ( TODAY () - 1825 )
                && 'M1 Notifications'[Created on]
                    > ( TODAY () - 365 )
        )
    )
VAR Res_1 =
    FILTER (
        Res_2,
         ( 'M1 Notifications'[Notification System Status] = "Outstanding notification"
            || 'M1 Notifications'[Notification System Status] = "Order assigned"
            || 'M1 Notifications'[Notification System Status] = "Notification in process" )
    )
RETURN
    IF ( COUNTROWS ( Res_1 ) <> BLANK (), COUNTROWS ( Res_1 ), UNICHAR ( ( 32 ) ) )

[kkanda · Accepted solution]

Thanks for the correction.