DAX MIN Count by Grouping Difficulty

[Anonymous]

Hello DAX pros!

I have what seemed to me like an easy calculation but I'm running into issues. I have below a dummy sample of sales agreements our company uses. Each one has a date, sales agreement number, one or more work orders associated to it, and a completion date if completed otherwise it is null. I have a requirement where I need to calculate the number of completed work orders where the first work order completed for each sales agreement would get a 0 in the column, and all other ones would get a 1. The work orders circled in red below would get a 1 since they are the first work order in the grouping. Reason for the grouping is because commission percentage is different based on the first completed work orders and subsequent work orders. Does anyone have any experience doing this? Any help would be appreciated! 

Thanks in advance! 

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Count = 
CALCULATE(
    DISTINCTCOUNT('Table'[Work Order]),
    FILTER(
        ALL('Table'),
        'Table'[Sales Agreement]=SELECTEDVALUE('Table'[Sales Agreement])&&
        'Table'[Work Order]<=SELECTEDVALUE('Table'[Work Order])&&
        'Table'[Work Order Completion Date]<>BLANK()
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

What exact end result are you expecting?

[parry2k]

@Anonymous what defines the first work order in an agreement, the lowest number of the work order within that agreement?

[Anonymous]

Yes, the lowest in the set of work orders