Forum Discussion
Relationship between USERELATIONSHIP and filters
Hi,
This has been solved, but not in a way I particularly like. It comes down to the relationship between FILTER and USERELATIONSHIP and I don't fully understand it.
The "standard" series uses FILTER to choose from 3 ORs. I.e:m4 - Total Number CH and AP and TR =CALCULATE( [m1 - Total Number Change and RMA], FILTER('Model',[IDFirst2Chrs]="CH" || [IDLast2Chrs]="AP" || [IDFirst2Chrs]="TR" ) )When this is combined with USERELATIONSHIP, the code is:
=CALCULATE( [m4 - Total Number CH and AP and TR], USERELATIONSHIP('Model'[Lodge Date.DDMMYYYY],Calendar2[Date]) )This does not return correct results for months, but does return correct for the overall relationship.
My hypothesis was that the multiple FILTERS were the problem (M1R, which did not use FILTER, worked perfectly).
As such, I created a helper column in the Data model that emulated the FILTER function:
[TR CH AP Type] =IF([IDLast2Chrs]="AP",1,IF([IDFirst2Chrs]="TR",1,IF([IDFirst2Chrs]="CH",1,0)))I then recreated my core measure using this as a native filter rather than using FILTER:
m4a - Helper Column =CALCULATE( [m1 - Total Number Change and RMA], 'Model'[TR CH AP Type]=1 )I then combined this with USERELATIONSHIP:
=CALCULATE( [m4a - Helper Column], USERELATIONSHIP('Model'[Lodge Date.DDMMYYYY],Calendar2[Date]) )The result was that this new measure returned the correct results for the entire period AND individual months:
m4R - Total Number CH and AP and TR Received2 209 195 182 287 m4ar 105 78 104 287 I assume it is due to the execution of the FILTER and USERELATIONSHIP functions and their relative sequencing.
Hi
Some dummy data:
| ID | Category | Type | Lodge Date | Grant Date | |
| 1 | RMA | AP | 1/6/2019 | 15/10/2019 | |
| 2 | RMA | AP | 3/7/2019 | 16/10/2019 | |
| 3 | RMA | TR | 5/8/2019 | 17/10/2019 | |
| 4 | RMA | CH | 8/9/2019 | 18/10/2019 | |
| 6 | Non RMA | DE | 11/10/2019 | 19/10/2019 | |
| 6 | RMA | DE | 13/11/2019 | 20/10/2019 |
The standard date link is Grant Date to the calendar.
When I use M1R, I am filtering for type RMA and using USERELATIONSHIP to use LodgeDate instead. This returns the right value for both the overall reporting period and the individual months e.g.
| June | July | August | September | October | November |
| 1 | 1 | 1 | 1 | 1 |
5 total
When I use M4R, I further filter for CH, AP, TR from M1R. The result I am assuming
| June | July | August | September | October | November |
| 1 | 1 | 1 | 1 |
4 total
While I am getting the right total for the whole period with M4R e.g. 4 in total, the individual months are recording incorrect numbers eg they might show 2 in June or 3 in August. At times there seems to be a geometric pattern (e.g. the August Result might be reporting period total less September or something odd) but it does not appear to be consistent.
| m1R - Received | 135 | 110 | 122 | 367 |
| m4R - Total Number CH and AP and TR Received2 | 209 | 195 | 182 | 287 |
M4R is a subset of M1R so cannot have higher numbers. The full period results (367 and 287) are correct and for M1R the individual months add to 367. For M4R, the individual months do not.
Hi,
This has been solved, but not in a way I particularly like. It comes down to the relationship between FILTER and USERELATIONSHIP and I don't fully understand it.
The "standard" series uses FILTER to choose from 3 ORs. I.e:
m4 - Total Number CH and AP and TR
=CALCULATE(
[m1 - Total Number Change and RMA],
FILTER('Model',[IDFirst2Chrs]="CH" || [IDLast2Chrs]="AP" || [IDFirst2Chrs]="TR"
)
)
When this is combined with USERELATIONSHIP, the code is:
=CALCULATE(
[m4 - Total Number CH and AP and TR],
USERELATIONSHIP('Model'[Lodge Date.DDMMYYYY],Calendar2[Date])
)
This does not return correct results for months, but does return correct for the overall relationship.
My hypothesis was that the multiple FILTERS were the problem (M1R, which did not use FILTER, worked perfectly).
As such, I created a helper column in the Data model that emulated the FILTER function:
[TR CH AP Type]
=IF([IDLast2Chrs]="AP",1,IF([IDFirst2Chrs]="TR",1,IF([IDFirst2Chrs]="CH",1,0)))
I then recreated my core measure using this as a native filter rather than using FILTER:
m4a - Helper Column
=CALCULATE(
[m1 - Total Number Change and RMA],
'Model'[TR CH AP Type]=1
)
I then combined this with USERELATIONSHIP:
=CALCULATE(
[m4a - Helper Column],
USERELATIONSHIP('Model'[Lodge Date.DDMMYYYY],Calendar2[Date])
)
The result was that this new measure returned the correct results for the entire period AND individual months:
| m4R - Total Number CH and AP and TR Received2 | 209 | 195 | 182 | 287 |
| m4ar | 105 | 78 | 104 | 287 |
I assume it is due to the execution of the FILTER and USERELATIONSHIP functions and their relative sequencing.