Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to Solution.
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
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! 🙂
Hi @BlueSky
The below article will explain how to use variebles in DAX
https://www.sqlbi.com/articles/variables-in-dax/
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |