Distinct Count with grouping for calculated column

[lgs1983]

Hi All,

I've tried to search the answer to my question but have failed on the numerous paths I've followed so am hoping you are able to help.

I have the following columns and what I want to do is create a calculated column to provide a distinct count where for A where, B is different, so:

Column1   Mapping       COUNT

A                AA1               2

A                AA1               2

A                AA2               2

B                AA1               2

B                AA2               2

C                AA1               1

C                AA1               1

C                AA1               1

So hopefully this makes sense, for A there are two distinct different mappings provided, the same for B. For C though there is only one distinct mapping provided

[Anonymous · Accepted solution]

Try using this for Calculated column:

Count Column = 
VAR v_SeriesCnt = CALCULATE
( 
    DISTINCTCOUNT( Data[Mapping] ), 
    ALLEXCEPT( Data, Data[Column1] ) 
 )
 RETURN
 v_SeriesCnt

Thanks.

[lgs1983]

@Anonymous Wow, that work just as I wanted.

I'm sure it's going to be over my head but what does the VAR v_SeriesCnt do? I'm assuming v_SeriesCnt is the name of the variable.

[Anonymous]

That's correct. It is just a variable.

[lgs1983]

Why does the formula have to be inside the variable? What's it's impact?

[Anonymous]

It does not have to be inside a variable. You can write it without variable too.

Count Column =
CALCULATE
( 
    DISTINCTCOUNT( Data[Mapping] ), 
    ALLEXCEPT( Data, Data[Column1] ) 
 )

Thanks.

[lgs1983]

Cool.

So I'm going to try to explain this formula, please correct me (if you want to that is) where I've gone astray. 

So the ALLEXCEPT is used as a filter where in theory it only looks at Column 1 and rest of the formula looks at the distinct mappings for those?