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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Question about Variables (VAR)

Hi!
I have a calculated table that tells me if the client has sold more than 25K in the last 3 Quarters.

When I use variables, it doesn't work, when I don't use variables, it does....
Why??? It looks ugly without the variables...
With Variables:

Normalize = 
VAR Qm1 =
    CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) )
    )
VAR Qm2 =
    CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
    )
VAR Qm3 =
    CALCULATE (
        [SO Net],
        PREVIOUSQUARTER (
            PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
        )
    )
RETURN
        SUMMARIZECOLUMNS('DB'[End User Name (or Next Purchasing Entity)],
        "Normalize",
            SWITCH (
                TRUE (),
                CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) )
    ) > 25000
                    && CALCULATE (
        [SO Net],
        PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
    ) > 25000
                    && CALCULATE (
        [SO Net],
        PREVIOUSQUARTER (
            PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
        )
    ) > 25000, "Large Clients (Exclude to normalize)",
                "Other Clients"
            )
    )

 

 Without Variables:

Normalize = 
SUMMARIZECOLUMNS (
    'DB'[End User Name (or Next Purchasing Entity)],
    "Normalize",
        SWITCH (
            TRUE (),
            CALCULATE ( [SO Net], PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) ) > 25000
                && CALCULATE (
                    [SO Net],
                    PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
                ) > 25000
                && CALCULATE (
                    [SO Net],
                    PREVIOUSQUARTER (
                        PREVIOUSQUARTER ( PREVIOUSQUARTER ( LASTDATE ( 'DB'[Date] ) ) )
                    )
                ) > 25000, "Large Clients (Exclude to normalize)",
            "Other CLients"
        )
)
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

Looking at the two measures you wrote separately (with and without variables), the first measure with variables, although variables are defined, but does not use it in the later calculations. It seems that the formulas are the same except for the defined variable in these two measures. It is reasonable to assume that the two measures will return the same results...
You can check the following link to learn more about the use and caution of variables. Variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.

Use variables to improve your DAX formulas

Caution When Using Variables in DAX and Power BI

Best Regards

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

Looking at the two measures you wrote separately (with and without variables), the first measure with variables, although variables are defined, but does not use it in the later calculations. It seems that the formulas are the same except for the defined variable in these two measures. It is reasonable to assume that the two measures will return the same results...
You can check the following link to learn more about the use and caution of variables. Variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.

Use variables to improve your DAX formulas

Caution When Using Variables in DAX and Power BI

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

You are defining the variables but are not using them anywhere?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.