cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kkanda
Resolver I
Resolver I

Use of multiple variables in the same measure

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
2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User II
Super User II

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

View solution in original post

Thanks for the correction. 

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User II
Super User II

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

View solution in original post

Thanks for the correction. 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.