Grouping distinct counts together in a table visualization

[cingall3]

Hello,

I am trying to create some visualizations for my dataset. There is a category for ticket number. If there was a ticket filed for an issue, the string of numbers is listed here. If there was no ticket filed, the space is either left blank or says N/A. I would like to display a count of how many issues had tickets associated with them vs how many do not have a ticket associated. Ie grouping the categories Blank and N/A together and then grouping all the ticket filed ones together.

Right now the table looks like this:

Ticket Filed                 Count

Blank                           32

N/A                             70

32948023                     1

28304982                     1

20398402                     1

And so forth with about 80 different ticket number sequences all with counts of one. I would like it to say Yes 102 and No 80 if possible.

Thanks!

[mahoneypat]

Not sure if this is exactly what you are looking for, but please try this measure expression with your data in a card visual.

Tickets Yes No =
VAR blankNA =
    CALCULATE ( SUM ( Tickets[Count] ), Tickets[Ticket Filed] IN { "", "N/A" } )
VAR numbers =
    CALCULATE (
        SUM ( Tickets[Count] ),
        Tickets[Ticket Filed] <> ""
            && Tickets[Ticket Filed] <> "N/A"
    )
RETURN
    "YES " & blankNA & "   No " & numbers

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[cingall3]

Hi there, thanks for your quick reply. I realized after I posted that those field names were actaully renamed in just the table. Ticket Filed=ILR Case Number and Count=Case Number in the unchanged field names. Because of this, I changed the column names I gave you to the orginal names.

When I added a measure, I got this message:

The syntax for 'Yes' is incorrect. (DAX(Tickets Yes No =VAR blankNA = CALCULATE ( SUM ( Tickets[Case Number] ), Tickets[ILR Case Number] IN { "", "N/A" } )VAR numbers = CALCULATE ( SUM ( Tickets[Case Number] ), Tickets[ILR Case Number] <> "" && Tickets[ILR Case Number] <> "N/A")RETURN "YES " & blankNA & " No " & numbers)).

[v-yingjl · Accepted solution]

Hi @cingall3 ,

I have tested this measure and seemed work fine, please check the below sample:

Grouping distinct counts together in a table visualization.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[cingall3]

Hi,

I think the issue might be that my case number column values are written as year-number as in 18-001, 18-002, etc for 2018 cases and onward. When I change the type from text to whole numbers, it messed up the other tables I had set up. Other than that, it seemed to accept it once i put ' ' around the data table name. Thanks for giving it a shot!