Measure to show Duplicate occurences of the same combination of two columns

[anktaggrwl]

Is there a quick/easy way to write a measure that shows duplicate occurrences of the same combination of values from two columns?

i.e. I have the following table:

Account	Team Member	Role
A	John	Service
A	Michael	Sales
A	Susan	Operations
A	Mary	Sales
B	John	Service
B	Joe	Service
B	Mary	Sales
B	Alexis	Sales
C	David	Service
C	John	Operations
C	Susan	Service
D	Michael	Sales
D	Keith	Service

And want a table visual in PBI to show something like this:

Account	Role	RoleCount
A	Service	1
A	Sales	2
A	Operations	1
B	Service	2
B	Sales	2
C	Service	2
C	Operations	1
D	Sales	1
D	Service	1

I would then bake into the measure where RoleCount =1 return Blank, else, give me the number of occurences of the same values in Role under any given value of Account...

Doing this in M is not an option, and I'd prefer it to be a measure, since I have 400k accounts, and 38 possible roles - so would want users to only focus/filter via slicer accounts on which he/she is attached.

Thanks!

[Vvelarde · Accepted solution]

@anktaggrwl

Hi, try with this in a table visual with Account and Role:

Measure =
IF (
    DISTINCTCOUNT ( Table1[ Team Member] ) = 1;
    BLANK ();
    DISTINCTCOUNT ( Table1[ Team Member] )
)

Regards

Victor