Count distinct based on condition

[Anonymous]

Hello all,

I have been struggling for a couple of days with the following case:

This is my data:

PART NO	ORDER NO	TRANSACTION DATE	QTY
ABC	1001	4/11/2020	200
ABC	1001	4/11/2020	200
ABC	1001	5/11/2020	200
ABC	1004	6/11/2020	200
ABC	1004	9/11/2020	200
CDE	1009	7/11/2020	300
CDE	1009	8/11/2020	300

My intention is to calculate how many different orders that were produced. In theory this would be a distinct count, but in practice, as with case order no = 1004 when the order was paused and continued after order 1009 was done. The accurate result would be to calculate that these were two separate orders.

The correct result (order count) would be:

1001 = 1 

1004 = 2

1009 = 1

--> 4 instead of a distinct count that would result in 3.

Any takes on a solution for this? I am thinking that based on transaction date one could say that if the order no is the same and date since last transaction for this product is within 1 day, then it is one, else it counts how many different days it was produced on.

Thanks in advance for any help!

[V-lianl-msft · Accepted solution]

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.

[V-lianl-msft · Accepted solution]

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.

[Anonymous · Accepted solution]

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!

[Anonymous]

Another way of thinking about would be to count the distinct DAYS with the exception of if the same order is produced for several days. Maybe that would help to think it the other way around?

[amitchandak]

@Anonymous , a column like this will give you 0 for for date diff greater than 1. See if this can help

Column =
var _1 = datediff(maxx(FILTER('Table','Table'[ORDER ]= EARLIER('Table'[ORDER]) && 'Table'[Date] < EARLIER('Table'[Date])),'Table'[Date]) , 'Table'[Date],day)
return if(_1 <=1,1,0)

[Anonymous]

Thanks for the reply. Did not work though.

I receive 1 for every case. I guess it would need to iterate based on the product column? Now order = earlier(order) would basically mean (if I've understood it correctly) that we take the date of the order before that?