topic Re: How to "flatten" a variable into constant? in DAX Commands and Tips

How to "flatten" a variable into constant?

Palmtop — Tue, 15 Nov 2022 15:24:41 GMT

Hello All,

I have 2 curves that contains a low and high case sales scenario. I would like to find the ratio between these 2 curves (Sales High / Sales Low) on the date of the latest monthly actual sales. I then want to use this ratio as a constant to multiply Sales Low over a range of dates.

Test_Measure = VAR _eval_date = CALCULATE ( MAXX ( ALLSELECTED ( Actual_Sales ), [date] ) ) // Finds latest sales date VAR _date = // If latest sales date is older than 2 months, it will default back to current month. CALCULATE ( IF ( _eval_date < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ), DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), _eval_date ) ) VAR _sales_high = CALCULATE ( // Value of sales high curve @ _date MAX ( Sales_High[sales] ), FILTER ( Sales_High, Sales_High[date] = _date ) ) VAR _sales_low = CALCULATE ( // Value of sales low curve @ _date MAX ( Sales_Low[sales] ), FILTER ( Sales_Low, Sales_Low[date] = _date ) ) VAR _ratio = DIVIDE ( _sales_low, _sales_high ) // Ratio of Sales Low/Sales High RETURN IF ( MIN ( Sales_Low[date] ) >= _date, CALCULATE ( SUM ( Sales_Low[sales] ) * _ratio ) ) // Multiply the Sales Low curve by _ratio for all future dates

I've included a sample file below:

Sample File Link

Lets say I get _ratio = 1.57

I would expect to get "Projected Sales" in the first figure as my final result. Instead I get the below figure, where I only have a single value. The curve in the first figure was made by replacing "SUM(Sales_Low[sales]) * _ratio" with "SUM(Sales_Low[sales]) * 1.57" meaning _ratio isnt actually a constant. I assume it has to do with the "IF Sales_Low[date] >= _date" condition.

Is there any way for me to 'flatten' the variable into a constant (1.57) that is invariable for all dates a.k.a. hardcode it?

I'm still very new to DAX so any additional advice and suggestions are very welcome.

Thank you.

Re: How to "flatten" a variable into constant?

daXtreme — Tue, 15 Nov 2022 13:40:38 GMT

First, I'd suggest you follow the rules for DAX formatting: Rules for DAX Code Formatting - SQLBI. DAX that's not formatted the right way is not DAX (and many specialist don't even look at such code). Second, I'd suggest you paste something that can be tinkered with and copied. Nobody wants to type text in from a picture. Third, I'd also kindly suggest that you create a sample file which demonstrates the issue and paste a link to it here so that it's easier for us to troubleshoot. The file should be stored on a shared drive and have public access rights.

Re: How to "flatten" a variable into constant?

Palmtop — Tue, 15 Nov 2022 15:28:31 GMT

Hello daXtreme,

Thanks for the advice. I've edited my post with the formatted DAX measure and a link to a sample file. Let me know if anyone has difficulties accessing it. Otherwise, I hope this is a good starting spot to help solve the issue.

Re: How to "flatten" a variable into constant?

daXtreme — Tue, 15 Nov 2022 17:19:16 GMT

Palmtop

That's much, much better now 🙂

Re: How to "flatten" a variable into constant?

daXtreme — Tue, 15 Nov 2022 19:51:41 GMT

File attached

Re: How to "flatten" a variable into constant?

Palmtop — Wed, 16 Nov 2022 13:32:58 GMT

Hi daXtreme,

Thank you, it worked like a charm. I appreciate all the help and patience. Also the clode cleanup is very welcomed 😉 .

I read through the date table page from PowerBI -

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

but still don't fully understand why is it exactly that auto-generated hierarchies in my initial model caused the issues it did, and how getting rid of them by making a date table fixed the issues.

Thanks again!

Re: How to "flatten" a variable into constant?

daXtreme — Wed, 16 Nov 2022 13:31:56 GMT

That's not exactly correct. It's not the auto-gen hierarchies that were the issue. It's the fields that were. You used the wrong fields from the wrong tables. As for the auto-gen hierarchies... JUST DON'T EVER USE THEM. Forget they exist. Always create your own Date tables. ALWAYS. Take this advice or suffer. Easy as that.