Forum Discussion
Help with Distintcount logic
I have the following table:
This table is basically capturing whether a job is finished or not. I am displaying the job vs date graph below:
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.
Distinct Count Measure = CALCULATE ( DISTINCTCOUNT ( 'Table'[finishNumber] ), FILTER ( 'Table', 'Table'[finishNumber] <> BLANK () ) ) + 0Add plus zero - it just forces the zero to show! :smileyhappy:
10 Replies
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)
Hope this helps! :smileyhappy:
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?
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.
