Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
TAUF
Frequent Visitor

DAX : why a single DAX with VAR does not work with FILTER

Hello people, I'ld like to improve my DAX level by using more variables within a formula. 

I'm working with Contoso 2 dataset.

I want to calculate a sales amount with filter applied : with 2 DAX, I get the correct answer (357,73M).

  • Sales Amount =

            SUMX(

                'Online Sales',

                'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount]

            )

  • Calculate 1 =

        CALCULATE(

            [Sales Amount],

            FILTER(

                'Online Sales',

                'Online Sales'[Unit Price] > 100 &&

                'Online Sales'[Discount Amount] > 10

                && RELATED(Products[Brand Name]) = "Contoso"

                )

        )

I wanted to use one DAX expression instead, however, the filter does not apply:

  • xx_Calculate_1 =

    VAR total_sales = SUMX(

                'Online Sales',

                'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount]

            )

    RETURN

    CALCULATE(

        total_sales,

        FILTER(

            'Online Sales',

            'Online Sales'[Unit Price] > 100 &&

            'Online Sales'[Discount Amount] > 10 &&

            RELATED(Products[Brand Name]) = "Contoso"

            )

    )

 Why my xx_Calculate_1 does not apply the filters ? 

TAUF_0-1745484049897.png

 

 I try to understand and change the formula but the result is always the same...

Do you have any suggestions to help me understand and to create a DAX expression ? 

Thank you.

1 ACCEPTED SOLUTION
sjoerdvn
Super User
Super User

Variables are not measures!
Your variable is a scalar value, so applying a filter does exactly nothing. If you want to combine the two, try:

CALCULATE(SUMX('Online Sales',
            'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount])
        ,'Online Sales'[Unit Price] > 100 
        ,'Online Sales'[Discount Amount] > 10
        ,Products[Brand Name] = "Contoso"
)

View solution in original post

2 REPLIES 2
TAUF
Frequent Visitor

Thank you so much for your reply and appreciate the reminder : variables are not measures !

sjoerdvn
Super User
Super User

Variables are not measures!
Your variable is a scalar value, so applying a filter does exactly nothing. If you want to combine the two, try:

CALCULATE(SUMX('Online Sales',
            'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount])
        ,'Online Sales'[Unit Price] > 100 
        ,'Online Sales'[Discount Amount] > 10
        ,Products[Brand Name] = "Contoso"
)

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.