Forum Discussion

ajisharavind_99's avatar
ajisharavind_99
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Avoid filter other Tables

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

 

DAX
Help Request
  • johnt75's avatar
    johnt75
    3 years ago

    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
    )

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Change the relationships from the date table to all the other tables to be one-to-many single-direction relationships.

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        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
    )