Last unique date per product and customer as a filter

[Marcusdahlberg]

I am trying to make a report based on my clients dustribution in each store that they sell products in. So its basically a table that when you select a specific item, you will see all the stores that had it in stock during the last check the seller did, and all the stores that didnt have it.  So something like this, select product on the left and then get a list with all stores that have the product "JA", and all stores that dont have it "NEJ".

My issue is that the datafile contains all the visits that the seller have done in each store. So i want to be able to only use the data from the last visit the seller did, not the old ones. By filtering on latest date i get most of them, but not all. Since not all stores get checked on the same days, i want to get each unique customers and products last date. So in this example:
Customer 35
Product: 59
Product exists: 0 (0 means NO) at the last date it was checked the 20/11-2019.

But i also want to get the Yes from the same customer but another product
Customer:35
Product: 63
Product Exists (1 = Yes) on the 13/3.2019.

The this list goes on for ever with lots of customers and products (think its about 2.000.000 rows). So i would like to hear if someone can help me out on how to get all the unique "Product Exists = 0 or 1" based on each products last checked date (per customer).

[v-alq-msft]

Hi, @Marcusdahlberg 

Based on your decription, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

ProductExists measure = 
var _maxdate = 
CALCULATE(
    MAX('Table'[AppointmentStartDateTime]),
    FILTER(
        ALL('Table'),
        'Table'[CustomerID]=SELECTEDVALUE('Table'[CustomerID])&&
        'Table'[ProductID]=SELECTEDVALUE('Table'[ProductID])
    )
)
return
CALCULATE(
    SUM('Table'[ProduceExists]),
    FILTER(
        ALL('Table'),
        'Table'[CustomerID]=SELECTEDVALUE('Table'[CustomerID])&&
        'Table'[ProductID]=SELECTEDVALUE('Table'[ProductID])&&
        'Table'[AppointmentStartDateTime]=_maxdate
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.