topic Re: Distinct count considering last date in DAX Commands and Tips

Distinct count considering last date

montilla — Mon, 13 Jan 2020 00:37:36 GMT

Hello friends!
I have the following product sales facts table:

Order	Product	Delivery Date	cStatus
1	Mouse	2019-12-01	Delayed Delivery
1	Keyboard	2020-01-01	Delayed Delivery
2	CPU	2019-12-20	Delayed Delivery

Using a count measure distinct by order number, I get the total orders delivered. In this example, the measure would return the total of 02 orders.

However, by plotting this measure on an axis line chart by date, the chart shows in the period the total of 03 orders delivered using the same previous measure because 1 order has deliveries on different dates.

I would like some help from you to know how to change this so that it shows in the graph the total of 02 orders, taking the largest date in the order that contains more than 01 delivery with different dates.

Can you help me?

Appreciate!

Below, the measure used:

mDelivered Orders = CALCULATE(DISTINCTCOUNT(FollowUp[Order]);USERELATIONSHIP(Calendar[Date];FollowUp[Delivery Date]);FollowUp[cStatus]="Delayed Delivery")

Re: Distinct count considering last date

montilla — Mon, 13 Jan 2020 02:21:51 GMT

Up! Someone?

Re: Distinct count considering last date

Anonymous — Mon, 13 Jan 2020 04:51:07 GMT

montilla Here is my approach.

First create a calculated column to get the max date per order.

Column = CALCULATE(MAX('FollowUp'[Delivery Date]),ALLEXCEPT('FollowUp','FollowUp'[Order]))

Modify your measure. i.e. use new column in relationship instead of delivery dates.

Measure = CALCULATE(DISTINCTCOUNT(FollowUp[Order]),USERELATIONSHIP('Calendar'[Date],FollowUp[Column]),FollowUp[cStatus]="Delayed Delivery")

If it helps accept as solution.

Re: Distinct count considering last date

montilla — Mon, 13 Jan 2020 11:30:04 GMT

Good morning everyone!

Dear Anonymous , your answer solved my problem. Thank you for your attention and support!
If you are not ordering too much, I would like to post a status column comparing whether within each order all items have a delivery date. If EVERYONE owns, throw in this column the value "Done". If there is at least 01 blank date, throw the value "Open". The column would only have the possibility of these 02 values.
Since I'm new to DAX, I still don't know how to get an array from an order's product list and compare if any date is blank. I have no idea how to do this considering order by order.

Could you help me with this problem?
Thanks again!

Re: Distinct count considering last date

Anonymous — Mon, 13 Jan 2020 11:42:53 GMT

montilla There are ways to do it. Here is the one approach

Column = VAR _order = CALCULATE(COUNT('Table'[Order]),ALLEXCEPT('Table','Table'[Order])) VAR _date = CALCULATE(COUNT('Table'[Delivery Date]),ALLEXCEPT('Table','Table'[Order])) RETURN IF((_order-_date)>0,"Open","Done")