Forum Discussion

cingall3's avatar
cingall3
New Member
6 years ago
Solved

Grouping distinct counts together in a table visualization

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!

Need Help

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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's avatar
      cingall3
      New Member
      6 years ago

      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)).