Forum Discussion
DAX Count Keys not working
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.
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 )
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! 🤠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.
4 Replies
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
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! 🤠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.