Using multiple USERELATIONSHIP functions as filters not working - why?

[D_PBI]

Hi,
I'm attempting to create a DAX measure that sums the amount of a column resulting from a union of three tables that are calculated within the same DAX measure.
The relationships that are relevant is desbribed below:
__dimDate[Date] 1-* agreement_forPotentialValue[Received Date] - this is inactive.

__dimDate[Date] 1-* agreement_forPotentialValue[Execution Date] - this is inactive.

I have a table created in Power Query named 'agreement_forPotentialValue'. This table contains many fields.
I've attempted to created a DAX measure (screenshot below) that performs three calculated tables. These three tables are unioned and the measure returns the summed value (Amount / Unit Price) from the union.

Finally, I want to create a table visual that has the agreement_forPotentialValue[Sub Team] in the rows sections and the measure value (so the summed amount from the DAX measure) to be in the column section. This way I will get the summed value by Sub Team.

It's not working. Please can someone validate if what I'm attempting to do is possible and how? 
Can someone please view my DAX measure and inform me if the DAX will do as I need. The DAX measure isn't failing - meaning no errors are showing.
I don't know why it isn't working. Thanks in advance.

[D_PBI]

@DataNinja777 - your suggested DAX is correct. I chose to use SUMX in my attempted measure but, with your help, I was able to progress to the point where I saw the need to use SUM instead of SUMX.
I think your accepted solution will help many as it shows how to use two USERELATIONSHIP functions in a single query. Thanks again.

[DataNinja777 · Accepted solution]

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,

[D_PBI]

@DataNinja777 - thank you for your reply. It is most helpful and very well explained. I've implemented your suggestion and the final number has changed from what I had originally (closer to the expected result) and the grouping is showing in the table visual. Unfortuntely, the returned number are a little higher than it should be. I will perform each individual table calculation to see if I can narrow down where the numbers are not matching.
I may report back if I find anything and seek further help. Thank you again.

[MFelix]

Hi @D_PBI ,

Without knowing the model and what is the rest of the context on your calculations is difficult to pinpoint the exact error. 

One thing that stoods out is the _tbl3 where you are using 2 USERELATIONSHIP that may be causing some incorrect calculations, but this may not be accurate.

I would debug this by going to the DAX viewer and pasting the 3 tables you have and  checking if you have results, to simulate the correct result I need to change the MIN and MAX by specific dates just to see if there are any results on your tables.

If you have results in the DAX view for each table then you need to check if you are missing any other context from a filter or a column on your model.