Forum Discussion

cityofglass88's avatar
cityofglass88
Frequent Visitor
8 years ago

Group by Total

Can't get my head around this requst and just looking to be pointed in the right direction.

I'm connected to a SQL dataset for ticket data. Relevant colums:
     Ticket ID |  User ID | Facility | *Lots of other rows*

I do a COUNTROWS on the fact table to return the number of tickets. I can then easily use SWITCH to categorize these based on the number of tickets a user submited (e.g. 1, 2-3, 3-10). Example table with these results:
     User ID | Tickets | Grouping
     User 1 | 2 | 2-3
     User 2 | 8 | 3-10
     User 3 | 9 | 3-10

 

Issue:
I am looking to use that grouping referenced above as rows in a matrix to give me:
     Grouping | Distinct Count User ID | Total Tickets

     2-3 | 1 | 2
     3-10 | 2 | 17

Because I am performing filtering in the report using the Facility and *Lots of other rows*, I can't figure out how to group or bin the User IDs into these defined groups baed on the COUNTROWS measure. With creating a related table with a distinct list of all User IDs and a calculated coumn for COUNTROWS, it ignored the filters applied from the face table. Any help or ideas would be greatly appreciated.

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi cityofglass88

     

    If I understand your scenario correctly, your expect result is the matrix visual you provide under the Issue.

     

    I try to reproduce the scenario as yours. There’s a simple way for you to group without write the SWITCH function, you could you use NEW GROUP to make a group. Then, do a measure like below:

    Tickets = DISTINCTCOUNT(Table1[Ticket ID ])

     

    If you don't know how to create group, you could refer to this article.

     

    The below picture is the my test result, you could refer to it.

     

    For details, you could refer to my demo pbix file here.

     

    Could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

     

    Best Regards,

    Cherry