Grouping of data 1

[Anonymous]

I have a table with user details:

ID	Activity ID
123	3
123	7
345	7
899	1
100	1
345	7
345	7

I want above table to be grouped based on ID = 7. Something like below

Count of ID with no Activity ID = 7	2
Count of ID with 1 Activity ID = 7	1
Count of ID with 2 Activity ID = 7	0
Count of ID with activity ID= 7 is >= 3	1

[v-robertq-msft · Accepted solution]

Hey, @prinkujose

Depending on your description, you can try these measures:

Count of ID with no Activity ID is 7 =

var _Table =

SUMMARIZE(

'Table',

[ID],

"Count",

COUNTX(FILTER('Table',[Activity ID]=7),[ID]))

var _count= COUNTX(FILTER(_Table,[Count]=BLANK()),[ID])

return IF(_count=BLANK(),0,_count)

Count of ID with 1 Activity ID is 7 =

var _Table =

SUMMARIZE(

'Table',

[ID],

"Count",

COUNTX(FILTER('Table',[Activity ID]=7),[ID]))

var _count= COUNTX(FILTER(_Table,[Count]=1),[ID])

return IF(_count=BLANK(),0,_count)

Count of ID with 2 Activity ID is 7 =

var _Table =

SUMMARIZE(

'Table',

[ID],

"Count",

COUNTX(FILTER('Table',[Activity ID]=7),[ID]))

var _count= COUNTX(FILTER(_Table,[Count]=2),[ID])

return IF(_count=BLANK(),0,_count)

Count of ID with 3 or more Activity ID is 7 =

var _Table =

SUMMARIZE(

'Table',

[ID],

"Count",

COUNTX(FILTER('Table',[Activity ID]=7),[ID]))

var _count= COUNTX(FILTER(_Table,[Count]>=3),[ID])

return IF(_count=BLANK(),0,_count)

And you can get whatever you want, like this:

You can download my test pbix file here

Best regards

Qin Community Support _Robert Team

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

What is your detail requirement, it's not clear to be honest. Thanks!

[Anonymous]

I would like to group table 1 based on activity ID 7. If you notice, ID with no actvity ID - 7 is 2 ie 899 and 100