cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rmsaunders Frequent Visitor
Frequent Visitor

Variable usage in CALCULATE statement

Hello. I'm new to Power BI and DAX, and I'm trying to get the following code more compact:
 
This works:
 
calc_TY_Sales_YTD =
VAR var_Metric = SELECTEDVALUE(table_Sales_Metrics[Metric])
VAR var_TY_sales_YTD =
SWITCH(TRUE(),
var_Metric = "Units", CALCULATE(SUM(SALES_TABLE[qty_sold]), DATESBETWEEN(DATE_TABLE[date], [calc_Report_Start_Date], [calc_Report_End_Date])),
var_Metric = "Cost $", CALCULATE(SUM(SALES_TABLE[dollar_sold]), DATESBETWEEN(DATE_TABLE[date], [calc_Report_Start_Date], [calc_Report_End_Date])),
var_Metric = "Retail $", CALCULATE(SUM(SALES_TABLE[retail_sold]), DATESBETWEEN(DATE_TABLE[date], [calc_Report_Start_Date], [calc_Report_End_Date])))
 
I'd rather do this...
 
calc_TY_Sales_YTD =
VAR var_Metric = SELECTEDVALUE(table_Sales_Metrics[Metric])
VAR var_Sales_measure =
SWITCH(TRUE(),
var_Metric = "Units", SUM(SALES_TABLE[qty_sold]),
var_Metric = "Cost $", SUM(SALES_TABLE[dollar_sold]),
var_Metric = "Retail $", SUM(SALES_TABLE[retail_sold]))
VAR var_TY_sales_YTD =
CALCULATE(var_Metric, DATESBETWEEN(DATE_TABLE[date], [calc_Report_Start_Date], [calc_Report_End_Date]))

...or this...
 
calc_TY_Sales_YTD =
VAR var_Metric = SELECTEDVALUE(table_Sales_Metrics[Metric])
VAR var_Sales_measure =
SWITCH(TRUE(),
var_Metric = "Units", SALES_TABLE[qty_sold],
var_Metric = "Cost $", SALES_TABLE[dollar_sold],
var_Metric = "Retail $", SALES_TABLE[retail_sold])
VAR var_TY_sales_YTD =
CALCULATE(SUM(var_Metric), DATESBETWEEN(DATE_TABLE[date], [calc_Report_Start_Date], [calc_Report_End_Date]))
 
...but neither of thise seem to work.
 
Am I stuck with the first method, or am I missing something?
 
Thanks.
1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Variable usage in CALCULATE statement

Hi @rmsaunders,

 

Do you have any performance issue? Or just want to compact the formula? If so, please try out this one. 

 

calc_TY_Sales_YTD =
VAR var_Metric =
    SELECTEDVALUE ( table_Sales_Metrics[Metric] )
VAR var_TY_sales_YTD =
    CALCULATE (
        SWITCH (
            var_Metric,
            "Units", SUM ( SALES_TABLE[qty_sold] ),
            "Cost $", SUM ( SALES_TABLE[dollar_sold] ),
            "Retail $", SUM ( SALES_TABLE[retail_sold] )
        ),
        DATESBETWEEN (
            DATE_TABLE[date],
            [calc_Report_Start_Date],
            [calc_Report_End_Date]
        )
    )
RETURN
    var_TY_sales_YTD

If it doesn't help, please provide a sample that we can download or copy with. Please mask the sensitive parts first.

 

 

 

Best Regards,

Community Support Team _ Dale
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

3 REPLIES 3
Super User
Super User

Re: Variable usage in CALCULATE statement

Hmm, probably a better way, can you share sample data that can be copied and pasted so that I can try a few things? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

 

Also, there are some techniques here that might help.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

v-jiascu-msft Super Contributor
Super Contributor

Re: Variable usage in CALCULATE statement

Hi @rmsaunders,

 

Do you have any performance issue? Or just want to compact the formula? If so, please try out this one. 

 

calc_TY_Sales_YTD =
VAR var_Metric =
    SELECTEDVALUE ( table_Sales_Metrics[Metric] )
VAR var_TY_sales_YTD =
    CALCULATE (
        SWITCH (
            var_Metric,
            "Units", SUM ( SALES_TABLE[qty_sold] ),
            "Cost $", SUM ( SALES_TABLE[dollar_sold] ),
            "Retail $", SUM ( SALES_TABLE[retail_sold] )
        ),
        DATESBETWEEN (
            DATE_TABLE[date],
            [calc_Report_Start_Date],
            [calc_Report_End_Date]
        )
    )
RETURN
    var_TY_sales_YTD

If it doesn't help, please provide a sample that we can download or copy with. Please mask the sensitive parts first.

 

 

 

Best Regards,

Community Support Team _ Dale
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

rmsaunders Frequent Visitor
Frequent Visitor

Re: Variable usage in CALCULATE statement

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 831 guests
Please welcome our newest community members: