Forum Discussion

Renjith's avatar
Renjith
Helper II
6 years ago
Solved

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...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

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