Forum Discussion
Solved
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]) ...
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,
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,
v-jiascu-msft:
I just wanted to compact the formula, and your solution has done that.
Thanks!
