Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm currently doing a table on PowerBI for the company I'm in. This table will display tickets worked by the agents, even when the tickets aren't closed/solved. It should also indicate if a ticket is billable or not, and the time spent on it ; a subtility is that a ticket can be worked on for 15 minutes billable and 15 others minutes non-billable, in which case it appears as one ticket being both. A ticket can also be worked on multiple times in a day and by the same agent, but in that case it should only be counted as one.
The distinction between billable and non-billable tickets is made via a "Billable" column in my datas, which can take the values True or False. In a previous version of this table, to distinguish billable from non-billable tickets, I would create two new columns, Billable True and Billable False (that may not be the best way to procede, but I'm still a beginner on PowerBI ^^'>)
and then count the True/False with a normal Count,
which allowed me, using this feature to get something like that
But there, each ticket is either billable or not, but can't be both. The main problem it had was that to differentiate two identical tickets, I sorted the lines by date, buffered it and eliminated the duplicates based on the ID. So, if the same ticket was worked on by various agents, it would only display for the last person who worked on it, for the last duration which was entered and with the last parameter about the billability.
To adress this issue, I created two additionnal columns, containing the ID of the tickets instead of True or False.
My reasoning was that, if when I use the Count feature it avoid the blanks box, I could use the Count (Distinct) feature
so a ticket appearing several times would appear only once, and the Blanks would still be ignored.
But I was wrong, and the Blanks are counted as one ^^'>
And now, the Blanks being counted whatever I do is messing the table
The Bill and !Bill values are wrong, Count of Billable True/False ID are supposed to replace them if I can make it works correctly, but as you can see I have innacurate values as Blanks are counted where I would prefer they weren't.
I have of course searched about my issue, but the only informations I could find are about Dax formulas, and I don't think (or don't see how) I could use it to solve my problem ; mine is specifically about the Distinct Count available with right-click on the values of the matrix.
So, if you have any idea, any knowledge you could share to me, I would greatly appreciate ^^'>
I can of course add more information if I haven't been clear enough (it's quite long but probably a little confused)
Thanks for reading me until then !
Solved! Go to Solution.
Hi @Anonymous , With a shorter example:
- I have this table:
- I want count only not blank register in "Count of Billable True ID". I have to create this measure:
I hope it works, best regards
Hi @Anonymous
You either create 2 measures to calculate the sum of field "Billable" = True and then = False, and use them both in your visaul
Or,
Create 2 measures using DISTINCTCOUNTNOBLANK of each column "Billable True" , "Billable False" and then use them both into your visual
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi @Anonymous
You either create 2 measures to calculate the sum of field "Billable" = True and then = False, and use them both in your visaul
Or,
Create 2 measures using DISTINCTCOUNTNOBLANK of each column "Billable True" , "Billable False" and then use them both into your visual
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi @Anonymous , With a shorter example:
- I have this table:
- I want count only not blank register in "Count of Billable True ID". I have to create this measure:
I hope it works, best regards
Hello Bifinity,
Your suggestion seems to be working as intendedon my dashboard !
Thank you for helping me, I thought it wouldn't work with a measure but I was wrong !
As my table get filled over time and start over at the beginning of each week, I'm not 100% sure it's all right, but if I notice anything that may raise additionnal question, I'll seek for help again, I guess ?
Thank you again for your help !
Horus
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
103 | |
77 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |