Forum Discussion

BlueSky's avatar
BlueSky
Helper I
6 years ago
Solved

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

 

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
)
)
 
Help required
  • Anonymous's avatar
    Anonymous
    6 years ago
    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

    D

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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

    D

    • BlueSky's avatar
      BlueSky
      Helper I
      6 years ago

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

    • BlueSky's avatar
      BlueSky
      Helper I
      6 years ago

      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.