Forum Discussion

montilla's avatar
montilla
Regular Visitor
6 years ago

Distinct count considering last date

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 ...
Date
DAX Calculation
Line chart
Measures
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

montilla's avatar
montilla
Regular Visitor
6 years ago

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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")