Exclude Blanks from Distinct Count when in a Matrix

[Anonymous]

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 !

[Bifinity_75 · Accepted 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:

Count = CALCULATE(COUNT('Table Example'[Count of Billable True ID]),

'Table Example'[Count of Billable True ID]<>BLANK())

- The result is:

I hope it works, best regards

[aj1973 · Accepted solution]

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

[Bifinity_75 · Accepted 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:

Count = CALCULATE(COUNT('Table Example'[Count of Billable True ID]),

'Table Example'[Count of Billable True ID]<>BLANK())

- The result is:

I hope it works, best regards

[Anonymous]

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