Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Sorry if I haven't asked the question in the best possible format, but it's quite difficult to explain. But I've got images to help explain my problem. Currently I've got a drilldown page that shows a number of tickets per organisation. This is what it usually shows below:
It currently shows displays the number of open tickets that are available for each Organization that I have. However, some Organizations have 1 ticket, or 0 tickets. And I want it to be formatted appropriately when a ticket is 1 or 0. So that it says "1 Open Ticket" instead of "1 Open Tickets", or if there are no open tickets, I want it to say that instead of "blank open tickets". How would I format that correctly? I'm assuming there must be a way to set such conditions but I can't find any in the filter/visualisation tools.
So basically, I want to avoid the tickets from looking like this below:
Any help would be appreciated in how to rectify this problem. I'm assuming a function will have to be made?
Solved! Go to Solution.
Hi @Anonymous
Download sample PBIX file with code/visuals shown below
What code are you using to create the current card visual?
Looking at your screenshots the Status column is text so presumaby this contains things like "Open" and "Closed" ?
OK so we don't know how you are calculating the number of open tickets but I'm guessing it will be something like this
Open Tickets = CALCULATE(COUNTROWS(RequestDetails), FILTER('RequestDetails', 'RequestDetails'[Status] = "Open"))
which returns a number.
I'm assuming that in the images above the text Open Tickets is the Category Label for the card? In which case you need to turn this off so the card just displays a number
Now we can alter the Open Tickets measure to account for 0/Blank, 1 or more than 1 open tickets.
Open Tickets =
VAR _OpenTickets = CALCULATE(COUNTROWS(RequestDetails), FILTER('RequestDetails', 'RequestDetails'[Status] = "Open"))
RETURN
SWITCH(
TRUE(),
_OpenTickets = 0, "No Open Tickets",
_OpenTickets = 1, "1 " & UNICHAR(00010) & "Open Ticket",
_OpenTickets & UNICHAR(00010) & " Open Tickets"
)
Regards
Phil
Proud to be a Super User!
Hi @Anonymous
Download sample PBIX file with code/visuals shown below
What code are you using to create the current card visual?
Looking at your screenshots the Status column is text so presumaby this contains things like "Open" and "Closed" ?
OK so we don't know how you are calculating the number of open tickets but I'm guessing it will be something like this
Open Tickets = CALCULATE(COUNTROWS(RequestDetails), FILTER('RequestDetails', 'RequestDetails'[Status] = "Open"))
which returns a number.
I'm assuming that in the images above the text Open Tickets is the Category Label for the card? In which case you need to turn this off so the card just displays a number
Now we can alter the Open Tickets measure to account for 0/Blank, 1 or more than 1 open tickets.
Open Tickets =
VAR _OpenTickets = CALCULATE(COUNTROWS(RequestDetails), FILTER('RequestDetails', 'RequestDetails'[Status] = "Open"))
RETURN
SWITCH(
TRUE(),
_OpenTickets = 0, "No Open Tickets",
_OpenTickets = 1, "1 " & UNICHAR(00010) & "Open Ticket",
_OpenTickets & UNICHAR(00010) & " Open Tickets"
)
Regards
Phil
Proud to be a Super User!
@PhilipTreacy Big thank you that worked, I think it was because I had label turned on. My mistake.
Can I ask just one quick question though? Other then "Open" I have two other values that go into the "Open" count. "Waiting on Customer" and "Waiting on Vendor" how do I put that into the Open count in that code? I've just tried adding it in but the Visual refuses to appear.
No worries @Anonymous
To include other Status as Open change the FILTER in the VAR line to this
VAR _OpenTickets = CALCULATE(COUNTROWS(RequestDetails), FILTER('RequestDetails', 'RequestDetails'[Status] IN {"Open", "Waiting on Customer", "Waiting on Vendor"}))
Regards
Phil
Proud to be a Super User!
Thanks very much Phil, very helpful!
Hi @Anonymous ,
You can use the following meausre :
Open Tickets = var vTickets = SUM(RequestDetails[Status])+0
Return IF(OR(vTickets<=1, ISBLANK(vTickets)), vTickets & " Open Ticket", vTickets & " Open Tickets")
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hey @v-deddai1-msft appreciate the reply.
I've just implemented this measure, I've caleld it (TicketMeasure), however, when I put it into the fields the visual doesn't work. I'll show you below.
In status I do already have some filters that only count a number of "open" tickets, maybe that could be interfering? But I'm not sure.
Try something like this
Open Tickets = var vTickets = [YourTicketsMeasure]
Return IF(OR(vTickets<=1, ISBLANK(vTickets)), vTickets & " Open Ticket", vTickets & " Open Tickets")
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi, thanks for the response. For some reason the new measure I'm creating for this isn't working, it can't pick up the Open Ticket which is filtered by "Status" column.
User | Count |
---|---|
89 | |
88 | |
85 | |
81 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |