Strange behavior in CALCULATE with USERELATIONSHIP and FILTER

[igor_r_arangues]

I have a fact table with four main columns:

• “Type”, indicating whether the row is “Plan” or “Real”
• “Billed Date”
• “Ship Date”
• “Volume”

The fact table is related to my dCalendar table through the "Billed Date" column. However, I need to create a visual that shows each day of the current month alongside the real shipped volume per day.

The issue is that I’m getting two different results with my measures, and I cannot understand why.

Here’s what I tried:

First Attempt

Volume = SUM(fact[volume])

1ºTry = CALCULATE([Volume], USERELATIONSHIP(dCalendar[Date], fact[Ship Date]))

Then, I created a table visual, placed dCalendar[Date] in the rows, and added a slicer on the “Type” column, filtering it to “REAL”.

✅ Result: This method works as expected — I get the correct daily volumes.

Second Attempt (Expected to be better):

2ºTry = CALCULATE([Volume], USERELATIONSHIP(dCalendar[Date], fact[Ship Date]), fact[Type] = "REAL")

However, when I use this measure in the same visual, I only get values for the days where the Ship Date and the Billed Date are the same. For all other days, the result is blank.

Third Attempt (I don’t know why it works):

3ºTry = CALCULATE([Volume],USERELATIONSHIP(dCalendario[Date],fact[Ship Date]),fact[Type]="REAL")

When I delete the FILTER DAX and use the “native” filter from CALCULATE, the measure works. Why???

My understanding and question:

I expected that adding fact[Type] = "REAL" as a filter inside CALCULATE would behave the same as applying the slicer at the visual level — but it doesn't. Instead, it seems to further restrict the context and only shows rows where both the active relationship on Ship Date and the Type filter apply, but in a way I didn’t anticipate.

Can anyone help me understand why this is happening?

Is there a difference in how context transitions or row filters are applied in this scenario with USERELATIONSHIP + FILTER compared to using a slicer?

Also, what would be the best practice for handling this kind of situation — should I keep relying on slicers for these filters, or is there a more robust way to incorporate the Type filter within the measure?

Thanks in advance!

[v-tejrama]

Hi  @igor_r_arangues,
Thanks for reaching out to the Microsoft fabric community forum.

In my report, I have two relationships between the calendar table and the fact table:

one active relationship between fact[billed date] and dcalendar[date]

one inactive relationship between fact[ship date] and dcalendar[date]

since I needed to analyze shipped volume by ship date, I used userelationship in my measure to activate the ship date connection.

Here’s the DAX measure I used:

_4try =
calculate(
[volume],
userelationship(dcalendar[date], fact[ship date])
)

My base measure for volume is simple:

volume =
sum(fact[volume])

This setup works perfectly because it uses ship date for the calculation and still respects any slicers, like the one on type (real or others). 

Please find the below attached Pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Tejaswi.
Community Support Team.

[johnt75]

The code for your 2nd and 3rd tries is the same, but from what you wrote and my understanding of the problem the 2nd try was probably something like

2ºTry =
CALCULATE (
    [Volume],
    USERELATIONSHIP ( dCalendar[Date], fact[Ship Date] ),
    FILTER ( 'fact', fact[Type] = "REAL" )
)

 The first thing to understand is that when you use the shorthand method of specifying a filter in CALCULATE, as in your 3rd attempt, the DAX engine automatically translates that into

2ºTry =
CALCULATE (
    [Volume],
    USERELATIONSHIP ( dCalendar[Date], fact[Ship Date] ),
    FILTER ( ALL ( 'fact'[Type] ), fact[Type] = "REAL" )
)

The crucial difference is that this version has ALL( fact[Type] ).

The second thing to understand is the order in which CALCULATE does things. First it evaluates the filters. Second, it applies any CALCULATE modifiers. Finally it applies the filters.

When the filters are evaluated the active relationship is between date and billed date, so only rows with a matching billed date and where type = real will be returned. This is why you only see rows where the billed date and the ship date are the same.

The measure works when using the shorthand version because of the ALL. That forces FILTER to ignore any filters on the dates, so it is returning all rows where type = real, regardless of date. 

With regards to best practice, it is best practice to use the shorthand version, partly because that enforces another best practice, which is to never filter entire tables but only filter columns or combinations of columns. Filtering entire tables is slower and less efficient and can lead to unexpected results, particularly if dealing with multiple fact tables.

[igor_r_arangues]

Thanks, it seems we’re getting closer to the answer. Could you please explain why I get this result with the 4ºTry, which is exactly what you described about the DAX engine?

4ºTry = CALCULATE([Volume],USERELATIONSHIP(dCalendar[Date],fact[ship date]),FILTER(ALL(fact),fact[Type]="REAL"))

[johnt75]

That code is using ALL( fact ), filtering the entire table. It will return all the rows of the table regardless of any filters on the date, so it will return the same value everywhere.

You need to use ALL( fact[Type] ), which will remove the filters just on that column but will leave all other filters in place.

The measure code should read

4ºTry =
CALCULATE (
    [Volume],
    USERELATIONSHIP ( dCalendar[Date], fact[ship date] ),
    FILTER ( ALL ( fact[Type] ), fact[Type] = "REAL" )
)

[igor_r_arangues]

One more:

"The measure works when using the shorthand version because of the ALL. That forces FILTER to ignore any filters on the dates, so it is returning all rows where type = real, regardless of date. "

When you said that I didn't understand. What is the relation between clean/ignore the Type column filter and ignore any filters on the (billed) dates?

[v-tejrama]

Hi @igor_r_arangues,

Thanks for reaching out to the Microsoft fabric community forum.

The issue happens because USERELATIONSHIP breaks the automatic filter flow from slicers like Type = "REAL".

Here’s the DAX measure I used:

CALCULATE(SUM(fact[Volume]), USERELATIONSHIP(dCalendar[Date], fact[Ship Date]))

To fix it, you need to include the filter explicitly:

Here’s the DAX measure I used:

CALCULATE(
SUM(fact[Volume]),
USERELATIONSHIP(dCalendar[Date], fact[Ship Date]),
fact[Type] = "REAL"
)

If you're displaying both "REAL" and "PLAN" together, use this:

Here’s the DAX measure I used:
SWITCH(
SELECTEDVALUE(fact[Type]),
"REAL", CALCULATE(SUM(fact[Volume]), USERELATIONSHIP(dCalendar[Date], fact[Ship Date])),
"PLAN", CALCULATE(SUM(fact[Volume]), fact[Type] = "PLAN")
)

This way, slicers work correctly, and your visuals show accurate results using the right date relationship.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Tejaswi.
Community Support Team 

[v-tejrama]

Hi @igor_r_arangues ,

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

[johnt75 · Accepted solution]

I've explained it poorly, let me try by explaining with some examples.

Just Filter =
CALCULATE (
    [Volume],
    USERELATIONSHIP ( dCalendar[Date], fact[ship date] ),
    FILTER ( fact, fact[Type] = "REAL" )
)

When just using the FILTER function it returns the expanded fact table as it as after merging the fact[Type] = "Real" condition into the current filter context. Any existing filter on the fact[Type] column would be overwritten but all other filters remain in place. This is calculated before any modifiers are applied, so the relationship from date to billed date is active. This results in a subset of the expanded fact table which includes only rows where billed date matches the date in the current filter context and where type = real.

Filter ALL Table =
CALCULATE (
    [Volume],
    USERELATIONSHIP ( dCalendar[Date], fact[ship date] ),
    FILTER ( ALL ( fact ), fact[Type] = "REAL" )
)

When combining FILTER with ALL on the table, rather than a column, this returns the expanded fact table where type = real, regardless of any other filters. So no matter what may be in the filter context for a given row in a visual, the result will always be the same - every row of the expanded fact table where type = real.

Filter ALL Column =
CALCULATE (
    [Volume],
    USERELATIONSHIP ( dCalendar[Date], fact[ship date] ),
    FILTER ( ALL ( fact[Type] ), fact[Type] = "REAL" )
)

When using FILTER with ALL on a column, or multiple columns, the behaviour is very different. It no longer returns a subset of the expanded fact table, it returns a table containing the valid values of the column which match the condition(s) you specify. In this case it would return a table with a single row and single column containing "Real".

The reason for the ALL in this case is to make sure that all possible valid values are considered. If you had values which only existed in rows which would be excluded by the current filter context, they would be ignored if ALL was not used.

This is still calculated before any modifiers are applied, so the relationship from date to billed date is still the active one, but that has no impact because the filter on date is ignored due to the ALL.

After the table containing the valid values has been calculated the relationship is changed to be from date to shipped date, and then the filter is merged into the filter context. So the date from the row in the visual is effectively applied as a filter on the shipped date column, and "Real" is applied as a filter on the type column.

The difference between FILTER returning a subset of the expanded table as opposed to valid values for a column(s) is key, and is one of the reasons why you should almost never filter an entire table, only filter the columns you are interested in. It is much more efficient and filtering the expanded table can produce unexpected results. You can read an excellent article at https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/ 

I hope this makes things a little clearer.