Refinement of CALCULATE measures with USERELATIONSHIP, to include FILTER & DISTINCTCOUNT

[MichaelHutchens]

Hi folks, I'm hoping someone can help. I have the below measure that I'm using the count total incidents using an inactive relationship:

INACTIVE_Count of Incidents created = CALCULATE ( COUNTA ('incidents'[Created By - Name] ), USERELATIONSHIP ('users'[Name], 'incidents'[Created By - Name] ) )

I'd like to re-use this measure with it's CALCULATE wraparound to create two new measures, with the below changes:

For the first measure:

• Instead of a COUNTA of 'incidents'[Created By - Name], it should be a DISTINCTCOUNT of 'deflections'[Viewed]
• I need to filter out results from a 'deflections'[user] column so that it only includes "string1" strings

For the second measure:

• Instead of a COUNTA of 'incidents'[Created By - Name], it should be a DISTINCTCOUNT of 'deflections'[Viewed]
• I need to filter out results from a 'deflections'[user] column so that it doesn't include "string2" or "string3" strings
  
  

I'm having trouble figuring out how to use FILTERs and DISTINCTCOUNTs in an inactive relationship, so any help would really be appreciated 🙂

[DataZoe · Accepted solution]

@MichaelHutchens You could try this approach:

Measure1 =
CALCULATE (
DISTINCTCOUNT ( 'deflections'[Viewed] ),
USERELATIONSHIP ( 'users'[Name], 'incidents'[Created By - Name] ),
'deflections'[user] = "string1"
)

Measure2 =
CALCULATE (
DISTINCTCOUNT ( 'deflections'[Viewed] ),
USERELATIONSHIP ( 'users'[Name], 'incidents'[Created By - Name] ),
NOT ( 'deflections'[user] IN { "string2", "string3" } )
)