Count distinct based on condition

Hi Anonymous ,

Based on your description, I created an index column in the query editor as a filter condition to create a calculated column.

diff =
VAR _diff =
    CALCULATE (
        FIRSTNONBLANK ( 'Table (2)'[TRANSACTION DATE], 1 ),
        FILTER ( 'Table (2)', EARLIER ( 'Table (2)'[Index] ) < 'Table (2)'[Index] )
    )
VAR day_ =
    DATEDIFF ( _diff, 'Table (2)'[TRANSACTION DATE], DAY )
RETURN
    IF(ISBLANK(day_),0,day_)

Then create a measure like this:

Measure =
VAR diff_1 =
    CALCULATE ( DISTINCTCOUNT ( 'Table (2)'[ORDER NO] ), 'Table (2)'[diff] <= 0 )
VAR diff_2 =
    CALCULATE ( COUNT ( 'Table (2)'[ORDER NO] ), 'Table (2)'[diff] > 0 )
RETURN
    diff_2 + diff_1

Sample .pbix

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

Thanks for the input. I think that solution will be better than the one I derived from amitchandak's reply.

The solution I have now is really slow, so I might check out your solution later when I have time the time to improve it. This is the solution:

var _prod = Table[PART NO]
var _datesapplied = Table[DATE_TIME]
var _lastday = 
    maxx(
        FILTER(Table,
             Table[ProductKey] = _prod &&
            Table[DATE_TIME]  < _datesapplied),
        Table[DATE])
var _diff = DATEDIFF(_lastday, _datesapplied,DAY)
return 
SWITCH(TRUE(),
    _diff = 0,0,
    1)

 I then created a measure that calcuated the distinct ORDER NO where this column = 1

CALCULATE(
    DISTINCTCOUNT('Table'[SOURCE_REFKEY]),
    Table[Shop Order Count] = 1)

Thanks for the input!