Forum Discussion
Relationship between USERELATIONSHIP and filters
Hi all,
I have a list of all applications as my main data source. The PROCESSED date is linked to DATE in a separate calendar file. I also have an inactive secondary relationship between RECEIVED and DATE.
m1 is a DISTINCTCOUNT of all applications. This links PROCESSED date.
m1r is a DISTINCTCOUNT of received applications that uses USERELATIONSHIP to use received date instead. Code is simple:
=CALCULATE(
[m1 - Total Number Change and RMA],
USERELATIONSHIP('Model'[Lodge Date.DDMMYYYY],Calendar2[Date]
)
)
m1r works perfectly. Values for months line up with total:
| m1R - Received | 135 | 110 | 122 | 367 |
m4 is a subset of m1 with additional filters around application type. When using PROCESSED date i.e. primary m4, it works perfectly:
| m4 - Total Number CH and AP and TR | 91 | 57 | 68 | 216 |
The problem comes with m4r, which is designed to be the equivalent of m1r i.e. USERELATIONSHIP to get received date. The code for m4r is largely identical to m1r:
=CALCULATE(
[m4 - Total Number CH and AP and TR],
USERELATIONSHIP('Model'[Lodge Date.DDMMYYYY],Calendar2[Date])
)
However, there is a problem with m4r. When I filter for a specific period (a month, or multiple months), the number is correct. However, when I show multiple months, the values per month are incorrect:
| m4R - Total Number CH and AP and TR Received | 209 | 195 | 182 | 287 |
287 is correct. If I then filter m4r to only show the first month, instead of 209 I get:
| m4R - Total Number CH and AP and TR Received | 105 |
This is correct (105)
I tried to work out what was happening and assume that the total (for the entire reporting period) is correct, but somehow it is double counting applications for each month (e.g. the underlying count is a DISTINCTCOUNT, so ID1 occurring in July, August, and September would total to 1, but show as 1 in each month as well.
This doesn't seem to be the case. I created a test pivot using application ID as rows, and then the measure as the column (e.g. show 1 where TRUE). Doing this, and then simply summing the columns, gives me the correct results, but the total at the bottom of the table is incorrect e.g. in the following, there are 105 1s in the first month column, but the sum at the bottom is 287:
Any ideas on why m1r works perfectly, while m4r works for a single reporting period, but breaks when showing over multiple?
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.
3 Replies
Hi Anonymous ,
Can you please share a dummy file? If you can't, please share some sample data and your expected results that will make us understand clearly about your requirement. Thanks.
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.
