Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DistinctCount grouping by

Hi, I've never understood how to do a distinct count with a group by on a visual that has many fields included the one I want to distinct count. I explain my problem with an example:

I have this visual

1 | 2 | 3 | 4 

----------------

A | W | G | K

A | S | G | J

A | C | N | J

B | V | N | K

B | Q | N | J

B | L | I | K

B | D | O | K

 

I want to do a distinct count of 1 grouping by 3 on column 5 to have this result:

1 | 2 | 3 | 4 | 5

----------------

A | W | G | K | 2

A | S | G | J | 2

A | C | N | J | 1

B | V | N | K | 2

B | Q | N | J | 2

B | L | I | K | 1

B | D | O | K | 1

 

I tried a formula like this: 

result = CALCULATE(DISTINCTCOUNT('Table1'[1]), GROUPBY('Table2','Table2'[3]))
but the result I obtain is this:

1 | 2 | 3 | 4 | 5

----------------

A | W | G | K | 1

A | S | G | J | 1

A | C | N | J | 1

B | V | N | K | 1

B | Q | N | J | 1

B | L | I | K | 1

B | D | O | K | 1

 

What formula can I use with DISTINCTCOUNT? Thanks

DAX
distinctcount

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

     

    5 CC =
    VAR currentthree = 'Table'[3]
    VAR currentone = 'Table'[1]
    VAR groupbythree =
    FILTER ( 'Table', 'Table'[3] = currentthree && 'Table'[1] = currentone )
    RETURN
    COUNTROWS ( groupbythree )