Relationship between USERELATIONSHIP and filters

[Anonymous]

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?

[v-xuding-msft]

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.

[Anonymous]

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. 

[Anonymous · Accepted solution]

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.