TREATAS returns blanks but USERELATIONSHIP returns values

[daniyalmazhar5]

I have 3 tables:
1) Territories
2) Doctors
3) Visits

Territories filters Doctors based on TerritoryCodes, and Doctors filters Visits based on DoctorKeys. There is an inactive relationship between Territories and Visits based on TerritoryCodes as well.

The Visits table has some DoctorKeys that are missing in the Doctors table.

I have created 3 COUNTROWS measures:

[ActualCalls] =
COUNTROWS ( 'Visits' )

[ActualCallsTREATAS] =
CALCULATE (
COUNTROWS ( 'Visits' ) ,
TREATAS ( VALUES ( 'Territories'[Territory] ) , 'Visits'[Territory] ) )

[ActualCallsUSERELATIONSHIP] =
CALCULATE (
COUNTROWS ( 'Visits' ) ,
USERLATIONSHIP ( 'Territories'[Territory] , 'Visits'[Territory] ) )

The measures give the following results:

As expected, ActualCalls show 1,266 visits against a blank row due to the missing DoctorKeys in the Doctors table. But I expected TREATAS and USERELATIONSHIP to both be able break down those calls as the Territories table contains all the territories found in the Visits table. USERELATIONSHIP does this well, but TREATAS fails to pull in the required values (but still shows the same total as the other measures).

Can anyone please help explain what's going on?

[AlexisOlson · Accepted solution]

First, the totals all being the same makes sense because, in the total line, there is no filtering happening related to territory. The filtering paths don't matter since they aren't being used.

Now, what's up with TREATAS? In this measure, the Doctors table is still filtering Visits but now you've added an additional filter on top of that. In particular, Visits is still limited to the rows that match the Doctors table (which is why you don't see the 1,266 in the blank row). If you insert ALL ( Doctors ) into the TREATAS measure, then it'll probably match the USERELATIONSHIP measure.

[daniyalmazhar5]

Thanks Alexis!

I'd thought TREATAS would replace any existing filters and bypass existing relationships, but the ALL('Doctors') or REMOVEFILTERS('Doctors') does the trick 👍