Count of multiple IDs and Columns

[Anonymous]

Hi all,

I am very new to PowerID and have been researching and testing several solutions in the board without success. 

In the below dataset, I would like to create a table showing how many users (user name) have 1 wireless number, 2 wireless numbers, 3 wireless numbers etc. for 3/31/2022. (My actual dataset would go up to 4 wireless numbers for some users, but my extract below would show only up to 2 wireless numbers for some users.) Please help, thank you!!

Dataset

Wireless Number	User Name	Market Cycle End Date
202000096	EID1	3/31/22
202000084	EID10	3/31/22
202000036	EID100	3/31/22
202000024	EID101	3/31/22
202000100	EID101	3/31/22
202000060	EID102	3/31/22
202000027	EID11	3/31/22
202000068	EID13	3/31/22
202000130	EID13	3/31/22
202000096	EID1	2/28/22
202000084	EID10	2/28/22
202000036	EID100	2/28/22
202000024	EID101	2/28/22
202000100	EID101	2/28/22
202000060	EID102	2/28/22
202000027	EID11	2/28/22
202000068	EID13	2/28/22
202000130	EID13	2/28/22

What I would like to see

Number of User Names with Multiple Wireless Numbers	Number of Wireless Numbers
2	2
5	1

[CNENFRNL · Accepted solution]

Simple enough. First create a dynamic table based on the max number of wireless access a customer can use,

[Anonymous]

Thank you so much!! It works!!

[speedramps]

Hi LucieM200

Please click here to download a solution

Please click here to download a example 

You may have to tinker with it depending on what you want to do with dates.

This is how it works ...

Users with occurence =

// get the occurnce for sequential list 1,2,3,4,5

VAR myoccurence = SELECTEDVALUE('Occurence list'[Occurence])

 

// create a summary table list of usrers and the total wireless per users

VAR mysummary =

SUMMARIZE('Dataset',

'Dataset'[User Name],

"Totalforuser",COUNT('Dataset'[Wireless Number])

)

 

// filter the sumary list based on the occurence. For example filer jsut occurence = 3

VAR myfiteredsummary =

FILTER(mysummary, [Totalforuser] = myoccurence)

RETURN

// count the number of users in the filtered summary table

COUNTROWS(myfiteredsummary)

I have helped you, now please help me by giving kudos.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each solver will get the kudos they deserve. Thank you ! 😎