Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BlueSky
Helper I
Helper I

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
)
)
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

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

Mariusz
Community Champion
Community Champion

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



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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.