topic Re: Total of Measure using Hasonefilter and VAR in DAX Commands and Tips

Total of Measure using Hasonefilter and VAR

Anonymous — Fri, 11 Nov 2022 08:02:04 GMT

Hi all,

I am trying to optimize a measure for excess stock. The old way of doing it was a lot of different measures being used in other measures, that were used in other measures etc. This was very slow, and I am using VAR to do it in one measure. The last part of the measure is a Hasonefilter / SUMX in order to sum the measure per row and get the "correct" total.

ISSUE:

When I do this in one measure using VAR, it does not sum in the totals, but when I do it in two different measure, where I put the Hasone / SUMX part in a seperate measure, it does it correctly. QUESTION: Can I fix this so I can keep only one measure?

MEASURES:

Measure that does not sum:

Hasone_Excess_Stock_EUR =

VAR First_Day_Invoicelines = firstdate(Fact3_ABP_Invoicelines[Date])

VAR Invoiceline_CP_365_Days = IF(TODAY() >= Firstdate('Date Table'[Date]),Calculate(SUM(Fact3_ABP_Invoicelines[Invoice Line Costprice EUR]),datesinperiod('Date Table'[Date],TODAY(),-1,YEAR)))

VAR Full_Year_Forecast_Value = if(abs(datediff(First_Day_Invoicelines,TODAY(),DAY))<365,ROUND(Divide(Invoiceline_CP_365_Days, Weeknum(TODAY()-First_Day_Invoicelines,2))*52,0),Invoiceline_CP_365_Days)

VAR Stock_On_Hand_Costprice = Calculate(sum('Fact1_Inventory History_daybyday'[Costprice Value EUR]),'Fact1_Inventory History_daybyday'[Date of export] = Max('Fact1_Inventory History_daybyday'[Date of export]))

VAR Excess_Stock_Value = IF(Stock_On_Hand_Costprice-Full_Year_Forecast_Value<0,0,Stock_On_Hand_Costprice-Full_Year_Forecast_Value)

VAR Hasone_Excess_Stock_Value = if(HASONEFILTER('DIM1_ABP_Article Masterfile_MDM'[Product Code]),Excess_Stock_Value,sumx(values('DIM1_ABP_Article Masterfile_MDM'[Product Code]),Excess_Stock_Value))

RETURN

Hasone_Excess_Stock_Value

When I make another measure as the following, it works:

Hasonetest = if(Hasonefilter('DIM1_ABP_Article Masterfile_MDM'[Product Code]),[Hasone_Excess_Stock_EUR],sumx(Values('DIM1_ABP_Article Masterfile_MDM'[Product Code]),[Hasone_Excess_Stock_EUR]))

Re: Total of Measure using Hasonefilter and VAR

lbendlin — Sat, 12 Nov 2022 20:43:15 GMT

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

Re: Total of Measure using Hasonefilter and VAR

v-easonf-msft — Mon, 14 Nov 2022 05:41:47 GMT

Hi， Anonymous

Variables can be used anywhere, however, their result might not be always the same. Because they are evaluated in the context in which they are written.
Caution When Using Variables in DAX and Power BI

You can also refer to this similar thread.
Different results depending on dax in measure vs. same dax in variable

mahoneypat wrote:
When calculated as a variable first it is in the evaluation context of the visual. Putting the variable inside the CALCULATE(variable, FILTER(... does not recalculate the expression in the desired new evaluation context.

Best Regards,
Community Support Team _ Eason