DAX help : Count

[Renjith]

Hi 

please help on below requirements

my sample data

Sl no  Role  Region

1        G1     Aus

1        G2     Aus

1        G1     Aus

2        G1     Ind

2        G1     Ind

3        G1     US

3         G2   US

3        G3    US

3       G4     US

3       G1    US

4      G1    NZ

4      G1   NZ

Requirements

1. Want to find the Count of Sl No whose role is G1,without moving to other role, 

  Answer 2 ,(Sl no 2 & 4 , Sl no 1 & 3 starts in G1 and ends in G1 , but go through other roles)

2. want to get an output like this

Sl no   hop

1          2

2          1

3          4

4          1

hop defines number of roles assigned to each Sl no (for Sl no 1 , G1 & G2 entries are available , like this

[mahoneypat · Accepted solution]

Here are some expressions for your measures:

Hop = DISTINCTCOUNT(SI[Role])

For the other measure there are two ways to go.

Only G1 =
COUNTROWS (
FILTER (
DISTINCT ( SI[SI no] ),
AND (
ISBLANK ( CALCULATE ( COUNTROWS ( SI ), SI[Role] <> "G1" ) ),
NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( SI ), SI[Role] = "G1" ) ) )
)
)
)

Or here is a more general way to count the SI No that have only had one role. 

Only Same Role =
COUNTROWS (
FILTER ( DISTINCT ( SI[SI no] ), CALCULATE ( DISTINCTCOUNT ( SI[Role] ) ) = 1 )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat