Distinct Count of Ids Which Contains And Not Contains Filtered Ids

[Ewo]

Hi,

I m trying to get distinct count of UserId's which gives order after first order. 

I have OrderData table like this;

 

First Q is; how can i get Values of UserId on filtered Date? 
Second Q is; how can i use that Values on other filters like contains or not contains?

For Exmp. the result that i want to reach:
Filters =  Date : 11/24/18 , OrderStatus : Done

Users With First Order = (Rows) 23 , 25

User Count With First Order = 2

User Count Thats Gives Order Again = 1 (UserId 23 in exmp)  (I want to count with filter "after filtered date")

User Count Thats Not Give Order Again = 1 (UserId 25 in exmp) (I want to count with filter "after filtered date")

Thanks all

[v-cherch-msft]

Hi @Ewo

You may create 2 slicer table first. Then you may create measures to get the values.Attached the sample file for your reference.

Regards,

Cherie

[Ewo]

Hi,

Thanks for your support,

But that's not working as i wanted. This solution only gives results for selected date (not for dates).

Here is T-SQL Query;  
-Inner select (CTE) gives data like my first example above.
There are many ways to get this result with sql query. I used simple way SubSelect in this.

It comes so hard do that with DAX  :S

SELECT
*
FROM
	(
	SELECT
	uor.UserId as UserId
	,ROW_NUMBER() OVER(Partition By uor.UserId Order by uor.UserOrderId) as OrderSequence
	,MAX(uor.CreationDateTime) as OrderDate
	FROM T_UserOrder uor
	WHERE 
	uor.OrderStatus = 'Done'
	GROUP BY uor.UserId,uor.UserOrderId
	) as CTE
WHERE
	CTE.OrderDate between @startDateParam and @endDateParam
	AND CTE.OrderSequence = 1
	AND (Select COUNT(u.UserId) from T_UserOrder u where u.OrderStatus = 'Done' AND u.UserId = CTE.UserId) > @OrderCountParam