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.
Hi,
When I calculate cumulative progress, it is 0.25 percent greater than total due to the relationship between the date dimension table and the other two tables. The outcome is satisfactory when I disable the relationship with the 3Dmodel table.
How can a filter be applied only to the date table using DAX?
Overall_Total Actual Prgrs =
[Overall_Eng_Actual_Prgrs]+[Overall_3Dmodel_Actual_prgrs]+[Overall_Benchmark_Actual_prgrs]
Overall_Actual_Cumulative_Prgrs =
CALCULATE (
[Overall_Total Actual Prgrs],
FILTER (
ALLSELECTED ( 'DIM Calendar' ),
'DIM Calendar'[Date]
<= MAXX( FILTER( 'DIM Calendar',
'DIM Calendar'[Date] <= TODAY()),
'DIM Calendar'[Date])
)
)
Result when relationship is active
Data Model
Solved! Go to Solution.
Try
Overall_Actual_Cumulative_Prgrs =
VAR MaxDate =
CALCULATE ( MAX ( 'DIM Calendar'[Date] ), 'DIM Calendar'[Date] <= TODAY () )
RETURN
CALCULATE (
[Overall_Total Actual Prgrs],
ALLSELECTED ( 'DIM Calendar' ),
'DIM Calendar'[Date] <= MaxDate
)
Change the relationships from the date table to all the other tables to be one-to-many single-direction relationships.
Try
Overall_Actual_Cumulative_Prgrs =
VAR MaxDate =
CALCULATE ( MAX ( 'DIM Calendar'[Date] ), 'DIM Calendar'[Date] <= TODAY () )
RETURN
CALCULATE (
[Overall_Total Actual Prgrs],
ALLSELECTED ( 'DIM Calendar' ),
'DIM Calendar'[Date] <= MaxDate
)
My budget calculation was the problem. When I applied for the cumulative as of today, it was also filtering budget. I removed the filter (REMOVEFILTERS), and the problem has been resolved.
I appreciate your response
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |