Distinct Count of a column by other column criteria

[bhard]

Hi,

I just can’t seem to get this right.

I am trying to create a simple measure of the % of plans that are on track to be completed on time.

Let me break down the data:

We have locations. Each location has Action Plans. Each Action Plan has ten steps (Display Value). Each step has a due date. I have a column that calculates if a particular step is past due or not.

IP Items Past Due = if(and('Intelex IP'[Due Date] < today(),isblank('Intelex IP'[Completion Date])),1,0)

Due date has a date in it until the step is marked complete, then it turns blank. Completion date is blank until a step is complete, then it is populated by the date it was completed.

What I want to do is in a report, create a card that shows the number of Action Plans, where no step is past due. The number of plans On Track. So if any of the ten steps is overdue, the entire planis marked as past due.  My problem is that I am trying to construct this through the visualization pane and I can’t seem to get Plan level data.

I have a pie chart:

I get a distinct count of past due plans and on track plans.Each count is distinct on the Action Plan name but it isn't a distincount overall. For example, if Plan A has one overdue step and one on time step, the plan will appear once on the On Track column and once in the past due column. What I am trying to do is have that plan only appear in the past due column. 

Since every plan has at least one step on track, I keep getting to total count (55) in the on track count.

I don’t know if power query or DAX are the way to go. I am not sure where to start.

In the image below, one Action Plan is On track and one is Off Track.

Thanks.

[lbendlin · Accepted solution]

"I don’t know if power query or DAX are the way to go. I am not sure where to start."

If the result of the computation is immutable you use Power Query or a DAX calculated column.  If the result can be impacted by user interaction with the report you use a DAX measure.

it sounds that in your case a calculated column is the easiest solution.