topic Re: How to make this DAX code more efficient (less duplication). in DAX Commands and Tips

How to make this DAX code more efficient (less duplication).

BlueSky — Sat, 25 Apr 2020 13:00:14 GMT

The following table expression is working fine (it's to create exponential growth data for the Covid-19 virus, based on the virus doubling every x days.).

I'm still learning PowerBI and I'm struggling with variables (I know they work more as constants here) also not being able to do loops.

Could someone suggest how to make the code below more efficient, there is a lot of duplicaiton?

(The only values which are changing are:

"Country" :

Every 2 days,

Every 3 days,

Every 4 days,

Every 5 days.

and

The incremental factor,

0.5,

0.333,

0.25,

0.2

However I'm having to repeat a lot of these values. (I've tried using varibles, in various places but get errors).

DimStartEnd =

union(

ADDCOLUMNS(

GENERATESERIES ( 0, 10, 0.5 ) // Doubles every 2 days

, "Days", [Value]/0.5+1

,"Deaths",round(10 * POWER(1+1, [Value] ),0 )

,"Country", "Every 2 days"

,"KeyFactToDimStartEnd","Every 2 days" &"-"& [Value]/0.5+1

)

ADDCOLUMNS(

GENERATESERIES ( 0, 10, 0.333 ) // Doubles every 3 days

, "Days", [Value]/0.333+1

,"Deaths",round(10 * POWER(1+1, [Value] ),0 )

,"Country", "Every 3 days"

,"KeyFactToDimStartEnd","Every 3 days" &"-"& [Value]/0.333+1

)

ADDCOLUMNS(

GENERATESERIES ( 0, 10, 0.25 ) // Doubles every 4 days

, "Days", [Value]/0.25+1

,"Deaths",round(10 * POWER(1+1, [Value] ),0 )

,"Country", "Every 4 days"

,"KeyFactToDimStartEnd","Every 4 days" &"-"& [Value]/0.25+1

)

ADDCOLUMNS(

GENERATESERIES ( 0, 10, 0.2 ) // Doubles every 5 days

, "Days", [Value]/0.2+1

,"Deaths",round(10 * POWER(1+1, [Value] ),0 )

,"Country", "Every 5 days"

,"KeyFactToDimStartEnd","Every 5 days" &"-"& [Value]/0.2+1

)

Re: How to make this DAX code more efficient (less duplication).

Mariusz — Sat, 25 Apr 2020 13:20:39 GMT

Hi BlueSky

The below article will explain how to use variebles in DAX

https://www.sqlbi.com/articles/variables-in-dax/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: How to make this DAX code more efficient (less duplication).

BlueSky — Sat, 25 Apr 2020 13:52:39 GMT

I've already seen that article, the purpose of posting on here was for someone to help apply the theory in practice, which I've not been able to do without generating errors.

Re: How to make this DAX code more efficient (less duplication).

Anonymous — Sat, 25 Apr 2020 15:13:34 GMT

EVALUATE var __rates = SELECTCOLUMNS( {.5, 0.333, 0.25, 0.2}, "GrowthRate", [Value] ) var __upperLimit = 10 var __output = GENERATE( __rates, var __currentRate = [GrowthRate] return ADDCOLUMNS( GENERATESERIES(1, __upperLimit, __currentRate ), "Days", round( [Value] / __currentRate + 1, 0), "Deaths", round(__upperLimit * POWER(2, [Value] ), 0 ), "Country", COMBINEVALUES( " ", // delimiter "Every", round(1 / __currentrate, 0), "days" ), "KeyFactToDimStartEnd", COMBINEVALUES( " ", "Every", round(1 / __currentrate, 0), "days", "-", round( [Value] / __currentRate + 1, 0 ) ) ) ) return __output

Best

Re: How to make this DAX code more efficient (less duplication).

BlueSky — Sat, 25 Apr 2020 17:00:22 GMT

Anonymous thanks so much for writing this, it'll really help my learning! 🙂