Search code examples
powerbimaxcircular-dependencybetweenslicers

When I try to Set my Power BI "Slicer" to a "Between" Range and set the End Date as the MAX Date where Colum is not null, I get Circular Dependency

I followed the instructions from this question.

Setting moving default start and end Date in power BI

It works correctly, but I'm trying to set my Default End Date, as the MAX Date where a Integer Column is not null.

I created the following Measure:

last_date = CALCULATE(MAX('App Arrivals'[ReportDate]), 'App Arrivals'[Actual] <> BLANK())

When I try to Update the table with the Default Range for my second slicer:

ArrivalsPeriods = 
UNION(
    ADDCOLUMNS(
        DATESBETWEEN('Datelist'[Date], [last_date] - 60,[last_date]),
        "Type", "Default"),
    ADDCOLUMNS(
        CALENDAR(MIN('Datelist'[Date]),MAX('Datelist'[Date])),
        "Type", "Custom"
    )
)

I got this warning message :

A circular dependency was detected: ArrivalsPeriods[ArrivalsPeriods], 04cbb961-419a-41ac-9c6e-8b4e8201b23b, ArrivalsPeriods[Date], ArrivalsPeriods[ArrivalsPeriods].

And It doesn't allow me to link that ArrivalPeriods Table with the Arrivals Table for filtering.

I'm trying to set the MAX Date filtered by a not null column value.

Solution

  • I was able to avoid the Circular Dependency and the CALCULATE function with this MAXX Dax Function.

    MostRecentDate = MAXX(FILTER('App Arrivals', 'App Arrivals'[Actual] <> BLANK()),'App Arrivals'[ReportDate])