topic Re: DAX Counting Groups of Repetition in Row in DAX Commands and Tips

DAX Counting Groups of Repetition in Row

danial_mcgreevy — Tue, 25 Aug 2020 08:47:57 GMT

Hey

Not long into my DAX understanding and i've hit a brickwall.

I need to create a measure that counts if a RANDOM_PERSON_ID is repeated 5 times.

So, my measure value with the test dataset should be 1 (9662AF155704B3F7).

Note that the table is on a Tabular Cube and does not have a uniqueidentifier and has about 46 million records.

Any help greatly appreciated.

Thanks

Danny

Re: DAX Counting Groups of Repetition in Row

amitchandak — Tue, 25 Aug 2020 09:38:58 GMT

danial_mcgreevy , Try this measure with RANDOM_PERSON_ID in a visual

countx(filter(summarize(table,table[RANDOM_PERSON_ID], "_1",count(*)), [_1]>=5),[RANDOM_PERSON_ID])

Re: DAX Counting Groups of Repetition in Row

danial_mcgreevy — Tue, 25 Aug 2020 10:54:16 GMT

Huge thanks.

Slight amendment to handle strings but works a charm.

COUNTAX (
FILTER (
SUMMARIZE (
Table,
Table[RANDOM_PERSON_ID],
"EqualTo5", COUNTA ( Table[RANDOM_PERSON_ID] )
),
[EqualTo5] = 5
),
Table[RANDOM_PERSON_ID]
)

Kind regards

Dannt