Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sieed
Helper II
Helper II

Help with Distintcount logic

I have the following table: 

tbl1.PNG

 

This table is basically capturing whether a job is finished or not.  I am displaying the job vs date graph below: 
ff.PNG



In this graph, I would like to display a tooltip such that: 

- It should display a distinct count of the finishNumber column based on whether the task is complete or not complete. 

   For instance, on 3/1/2017, I have 2 completed tasks and one uncompleted task. The uncompleted tasks do not have finishNumber. So, in my bar graph, when I hover over the red area, the distinct count of finishNumber for the two completed tasks should be 2 ( since 14 and 16 are distinct). And if i hover over the black area, the distinct count should be zero (since there is no finishNumber for the very first record).

How can I accomplish this?  I am not sure what logic to implement and whether it should be a measure or a column? 

 

Would appreciate any help. 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

Distinct Count Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[finishNumber] ),
    FILTER ( 'Table', 'Table'[finishNumber] <> BLANK () )
)
    + 0

Add plus zero - it just forces the zero to show! Smiley Happy

View solution in original post

10 REPLIES 10
Sean
Community Champion
Community Champion

@sieed

This is what I would do...

Create this Measure (you can rename it something better)

Distinct Count Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[finishNumber] ),
    FILTER ( 'Table', 'Table'[finishNumber] <> BLANK () )
)

And then setup your Chart like in the picture below

Date on Axis, AppID in the Legend, Measure in the Value

(right-click on AppID in the Legend as check - Show Items with No Data)

Count of AppID.png

Hope this helps! Smiley Happy

Since he already have IsFinished flag, no need to calculate a measure assuming each job will have one record:

 

- Put date on the axi

- IsFinished on legent

- Cound Job Id on value 

 

and it will do the job, no?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sean
Community Champion
Community Champion

Use my Measure from above in the Tooltips!

It will show up only for true!

@Sean, what do I do if I also want to diaply the distincount of 0 ? 

Sean
Community Champion
Community Champion

Distinct Count Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[finishNumber] ),
    FILTER ( 'Table', 'Table'[finishNumber] <> BLANK () )
)
    + 0

Add plus zero - it just forces the zero to show! Smiley Happy

Thanks @Sean

@sieed what I mentioned will work, make sure you have IsFinished on Legend. No sure why there is a need to calculate a measure when you already have IsFinished to seperate between finished and non-finished jobs. Also it is assuming that you will unique finish number. It is always good to provide little context about the dataset so that better solution can be provided.

 

THanks,

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sean
Community Champion
Community Champion

@parry2kWhat do you propose we drag to the tooltips? And how will you make it show zero on False?

 

And BTW my measure would work with COUNTA not only DISTINCTCOUNT because EACH Column represents an APP

 

However here's why I used DISTINCTCOUNT - this is from the original question...

"the distinct count of finishNumber for the two completed tasks should be 2 ( since 14 and 16 are distinct)"

@parry2k, I am trying to find the ditinct count of "finishNumber" based on whether a task is completed or not for that particular date. 

 

For instance, on 3/12017, I have three tasks, two of which are complete. Moreover, these two completed tasks have different "finishNumber".  so, the distinct count should be 2 on the tool tip. Also, for the same bar on the same date, the black area (the area representing the record which is not complete) should show distinct count of zero since there is no "FinishNumber" for that record. 

@Sean,  the legend is the "IsFinished" column, not the AppID.  That's the chellenging part. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.