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.
Hello folks,
I have a complicated dax that works perfectly on its own when I visualize it in PBI (it consists of 3 measures). But I need to show this measure in different time comparisons so this is why I copied the measures to tabular editor and put together the three measures into one calculation item. There is no bug on the calculation item page, it is saved, but PBI matrix visual shows incorrect 0 values.
Here are the measures separately in PBI (original calculations):
Valuerunningtotalbysprintwk =
CALCULATE (
[Velocity],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] )
&& 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] )
)
)
ValuerunningtotalpreviousS =
VAR previousyearlastsprintwknumber =
MAXX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
),
'Calendar'[Sprint Wk CC]
)
RETURN
IF (
SELECTEDVALUE ( 'Calendar'[Sprint Wk CC] ) <> 1,
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC]
= MAX ( 'Calendar'[Sprint Wk CC] ) - 1
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
),
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
&& 'Calendar'[Sprint Wk CC] = previousyearlastsprintwknumber
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
)
)
MainKPI= DIVIDE(
Valuerunningtotalbysprintwk - ValuerunningtotalpreviousS,
ValuerunningtotalpreviousS
)
I have created variants to be able to put them together to the calculated items page (tabular editor):
VAR Valuerunningtotalbysprintwk =
CALCULATE (
SELECTEDMEASURE(),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] )
&& 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] )
)
)
VAR ValuerunningtotalpreviousS =
VAR previousyearlastsprintwknumber =
MAXX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
),
'Calendar'[Sprint Wk CC]
)
RETURN
IF (
SELECTEDVALUE ( 'Calendar'[Sprint Wk CC] ) <> 1,
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC]
= MAX ( 'Calendar'[Sprint Wk CC] ) - 1
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
),
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
&& 'Calendar'[Sprint Wk CC] = previousyearlastsprintwknumber
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
)
)
VAR RESULT =
DIVIDE(
Valuerunningtotalbysprintwk - ValuerunningtotalpreviousS,
ValuerunningtotalpreviousS
)
RETURN RESULT
The main purpose of this calculation is to see how the selected measure improved compared to the previous sprint, but also thinking about which day the report is open, because I want compare the actual sprint value on the latest day compared to the previous sprint with the same day running total.
If I only copy this part of the measure to tabular editor, the value shows up in PBI:
Valuerunningtotalbysprintwk =
CALCULATE (
SELECTEDMEASURE(),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] )
&& 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] )
)
)
Any help is appreciated, how I could fix the formula in editor to see the values in PBI.
Many thanks in advance!
Hi Xiaoxin,
thank you for your response. Yes, I was following this article when I created the calculations. My understanding is that I can't use the measures, but I need to add them one by one as variants to the calculated group. Although the bug might come from the order and complication of my calculation.
Hi @pbi_wannabe_exp,
You can take a look at the following link about using calculate group in power bi if helps:
Creating Calculation Groups in Power BI Desktop - SQLBI
Regards,
Xiaoxin Sheng
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 |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |