DAX Counting Groups of Repetition in Row

[danial_mcgreevy]

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

[amitchandak · Accepted solution]

@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])

[danial_mcgreevy]

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