Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello, i have 2 measures, the second one calls the first one but i need to optimize them since they are taking long time to compute with large data:
Measure 1:
Solved! Go to Solution.
@sarahomar955 , Try using variables
For measure 1
dax
L10 =
VAR Actuals_L10 =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[L10]"
)
)
VAR Actuals_GES =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[GES]"
)
)
VAR Actuals_L6a =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[L6a]"
)
)
RETURN
DIVIDE(Actuals_L10, Actuals_GES - Actuals_L6a, 0)
and for measure 2
Proud to be a Super User! |
|
@sarahomar955 , Try using variables
For measure 1
dax
L10 =
VAR Actuals_L10 =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[L10]"
)
)
VAR Actuals_GES =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[GES]"
)
)
VAR Actuals_L6a =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[L6a]"
)
)
RETURN
DIVIDE(Actuals_L10, Actuals_GES - Actuals_L6a, 0)
and for measure 2
Proud to be a Super User! |
|
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |