Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count of multiple IDs and Columns

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 NumberUser NameMarket Cycle End Date
202000096EID13/31/22
202000084EID103/31/22
202000036EID1003/31/22
202000024EID1013/31/22
202000100EID1013/31/22
202000060EID1023/31/22
202000027EID113/31/22
202000068EID133/31/22
202000130EID133/31/22
202000096EID12/28/22
202000084EID102/28/22
202000036EID1002/28/22
202000024EID1012/28/22
202000100EID1012/28/22
202000060EID1022/28/22
202000027EID112/28/22
202000068EID132/28/22
202000130EID132/28/22

 

 

What I would like to see

Number of User Names with Multiple Wireless NumbersNumber of Wireless Numbers
22
51
Need Help

3 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    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 ! ðŸ˜Ž

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much!! It works!!