Using multiple USERELATIONSHIP functions as filters not working - why?

Hi D_PBI ,

Yes, what you're attempting is possible, but the current DAX isn't working as you expect due to a couple of key issues related to how DAX handles filter context and relationships. The main problem is that your visual's context (the breakdown by [Sub Team]) isn't being applied to the virtual tables you create inside the measure.

When you place a column like agreement_forPotentialValue[Sub Team] on the rows of a visual, it creates a filter context for each row. However, your measure's logic constructs three separate virtual tables (_tbl1, _tbl2, _tbl3), unions them, and then uses SUMX to iterate over this final combined virtual table. This final table is disconnected from the original filter context of the visual. As a result, the measure calculates the same grand total and displays it for every single sub-team, rather than calculating a specific value for each one.

There is also a secondary issue in how you're using USERELATIONSHIP. Inside your _tbl3 variable, you call the function twice for two different relationships within the same CALCULATETABLE. DAX can only activate one inactive relationship between two tables in a single CALCULATE context. When you provide more than one, the last one in the argument list simply overrides any previous ones. In your case, the relationship to [Received Date] is overriding the one for [Execution Date], so that filter is being ignored.

CALCULATETABLE(
    agreement_forPotentialValue,
    USERELATIONSHIP(__dimDate[Date], agreement_forPotentialValue[Execution Date]),
    // The line below overrides the line above
    USERELATIONSHIP(__dimDate[Date], agreement_forPotentialValue[Received Date]),
    ...
)

The most effective solution is to restructure the measure to avoid creating and unioning large tables. Instead, you can calculate each part of the sum separately within variables using CALCULATE. This function is specifically designed to work with the visual's filter context correctly. This approach is more efficient, easier to debug, and correctly solves both the context and the relationship issues.

_Potential Value Measure =
VAR _MinDate = MIN( '__dimDate'[Date] )
VAR _MaxDate = MAX( '__dimDate'[Date] )
VAR _MinDateLY = _MinDate - 365
VAR _MaxDateLY = _MaxDate - 365

// Logic for the first table (_tbl1)
VAR _Value1 =
    CALCULATE(
        SUMX(
            agreement_forPotentialValue,
            agreement_forPotentialValue[Amount] / agreement_forPotentialValue[Unit Price]
        ),
        USERELATIONSHIP( '__dimDate'[Date], agreement_forPotentialValue[Received Date] ),
        DATESBETWEEN( '__dimDate'[Date], _MinDate, _MaxDate ),
        agreement_forPotentialValue[Agreement Type] = 100
    )

// Logic for the second table (_tbl2)
VAR _Value2 =
    CALCULATE(
        SUMX(
            agreement_forPotentialValue,
            agreement_forPotentialValue[Amount] / agreement_forPotentialValue[Unit Price]
        ),
        USERELATIONSHIP( '__dimDate'[Date], agreement_forPotentialValue[Received Date] ),
        DATESBETWEEN( '__dimDate'[Date], _MinDateLY, _MaxDateLY ),
        agreement_forPotentialValue[Agreement Status] IN { "Preliminary", "Full Application", "Submitted" },
        agreement_forPotentialValue[Agreement Type] = 100
    )

// Logic for the third table (_tbl3), correcting the multiple USERELATIONSHIP issue
VAR _Value3 =
    CALCULATE(
        SUMX(
            agreement_forPotentialValue,
            agreement_forPotentialValue[Amount] / agreement_forPotentialValue[Unit Price]
        ),
        // Activate the relationship for Execution Date
        USERELATIONSHIP( '__dimDate'[Date], agreement_forPotentialValue[Execution Date] ),
        DATESBETWEEN( '__dimDate'[Date], _MinDate, _MaxDate ),
        
        // Apply the filter for Received Date separately
        FILTER(
            ALL( agreement_forPotentialValue[Received Date] ),
            agreement_forPotentialValue[Received Date] >= _MinDateLY && agreement_forPotentialValue[Received Date] <= _MaxDateLY
        ),
        agreement_forPotentialValue[Agreement Type] = 100
    )

// The final result is the sum of the individual calculations
VAR _result = _Value1 + _Value2 + _Value3

RETURN
    _result

This revised measure works because each CALCULATE expression respects the initial filter context from your visual (e.g., [Sub Team] = "Sub Team A") before applying its own modifications. Furthermore, in the _Value3 calculation, the multiple date filter logic is handled correctly by activating one relationship with USERELATIONSHIP and applying the second date condition using an explicit FILTER function. Adding these three results together gives you the final, correct value for each row in your visual.

Best regards,