DAX Count Keys not working

[EPower21]

I want to calculate a distinct count of keys for each user (Pos ID) for each month (KEY).
I tried a couple of different approaches, Count 1, Count 2, Count3, but these do not give me the expected value, which I show here in the column, Key Count. The first 18 rows should show 2 and the last 2 rows should show 1.

CountKeys.pbix 

Count1 =
VAR Key1 = 'FACT'[KEY]
RETURN CALCULATE( DISTINCTCOUNT('FACT'[KEY]), FILTER( ALL('FACT'), 'FACT'[KEY] = Key1 )   )

Count2 =
VAR SummaryTable = GROUPBY ( FILTER ( 'FACT', 'FACT'[Pos ID] = EARLIER ( 'FACT'[Pos ID] ) ), 'FACT'[KEY]     )
RETURN COUNTROWS ( SummaryTable )

Count3 =
VAR SummaryTable = GROUPBY (FILTER ( 'FACT', 'FACT'[KEY] = EARLIER ( 'FACT'[KEY] ) ), 'FACT'[KEY] )
RETURN
COUNTROWS ( SummaryTable )

[adudani · Accepted solution]

hi @EPower21 ,

via DAX: 

key_count = 



COUNTROWS(
FILTER('FACT',
'FACT'[KEY]= EARLIER('FACT'[KEY])
)
)

[Daniel29195 · Accepted solution]

@EPower21 

use this code  :

Column = 

CALCULATE(
    COUNT('FACT'[KEY]) ,
     ALLEXCEPT('FACT','FACT'[KEY])
)

your first measure should work 

the issue is that you are counting distinct key,  so it should return 1 not 2 . 

since the first 2 rows, the keys are equal . 

the code i have provided will count ( not distinct ) 

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Anonymous · Accepted solution]

Hi @EPower21 ,

You can also create a calculated column.

_Key Count = 
CALCULATE(
    COUNT('FACT'[KEY]) ,
    'FACT'[KEY]=EARLIER('FACT'[KEY])
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Daniel29195 · Accepted solution]

@EPower21 

use this code  :

Column = 

CALCULATE(
    COUNT('FACT'[KEY]) ,
     ALLEXCEPT('FACT','FACT'[KEY])
)

your first measure should work 

the issue is that you are counting distinct key,  so it should return 1 not 2 . 

since the first 2 rows, the keys are equal . 

the code i have provided will count ( not distinct ) 

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[adudani · Accepted solution]

hi @EPower21 ,

via DAX: 

key_count = 



COUNTROWS(
FILTER('FACT',
'FACT'[KEY]= EARLIER('FACT'[KEY])
)
)

[adudani]

hi @EPower21 ,

I have attempted this via Power query.

Kindly copy and paste the code below into the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMDBU0oHQCjUKXo5+QNLIwMgYKGikFKtDlhI3VycqKPF1DKKCEscAaijxdYykghKvUMKhS4wSHyoocQx1p4KSYNcAKijxdw5BKDHEqsTPP4yQEhdXZxQlsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Pos ID" = _t, KEY = _t, #"Key Count" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pos ID", type text}, {"KEY", type text}, {"Key Count", Int64.Type}}),
    #"Grouped Rows by Key" = Table.Group(#"Changed Type", {"KEY"}, {{"Data", each _, type table [Pos ID=nullable text, KEY=nullable text, Key Count=nullable number]}, {" Row Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows by Key"

let me know if this works for you.

output