2 Questions using DAX

[Anonymous]

Hey, 

I Have 5 tables:

1. DimDate (Date table)

2. Orders

3. Order Details

4. Products

5. Suppliers

Here's the tables and the connections between them:

Now I want to retrieve 2 pieces of information from those tables:

1.  The names of the suppliers who did not purchase anything from their products during 2017 (They probably do not appear in the Orders table)

2. The names of the products that were not sold at all in 2018 (again, They probably do not appear in the Order Details table).

Which DAX formula can I use to check which of them DOES NOT appear in the order tables?

Thanks!

[Anonymous]

Thanks Guys!

1 last question: every customer has couple of orders in Orders table. i want to check the average days between ALL of his orders.

How can I check it? I need to check for each order what is the next order date and to check again the DATEDIFF, but the customer has as i said couple of orders.

Thanks!

[andre]

here is a tutorial for Average Days Between Orders calculation:

you can download the dataset and the model to practice here: https://businessintelligist.com/2020/07/26/dax-tutorial-average-days-between-orders/

[andre]

here is the video that explains how to count things that did not happen:

[mahoneypat]

One way would be to use a simple IF() in a table visual with your Supplier names.  

No 2017 Purchases =
IF (
ISBLANK ( CALCULATE ( COUNTROWS ( 'Order Details' ), 'Dim Date'[Year] = 2017 ) ),
1
)

The table visual should then only show those suppliers with no purchases in 2017.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat