Forum Discussion
DAX help : Count
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
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
1 Reply
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