Latest Enterprise DNA Initiatives

Filter within one table


I am working with a Sales table which has a Sales Order Dates column and a Product Key column (among other columns - it is the Sales table from the Collie book).

Firstly I am calculating the first date of the table with the following measure that works:

Then I want the same value but for one specific product Key, 214 in my example. So I want the first date for which the product 214 was sold. I put the following measure but it does not work:

I don’t understand why it retrieves the dates whitin the filter context as the base measure specifies ALL dates:


But when I change the measure with an ALL as below, it works:

Could anyone please explain the sublte difference?

Thank you for your help!

Hello @Pbovet,

Thank You for posting your query onto the Forum.

Firstly, this is the first formula that you’ve written to evaluate the results for the Product Key 214 -

'First Date Of Sales Product 214 = 
CALCULATE( [Min date sales table] , 
   FILTER( Sales , 
      Sales[ProductKey] = 214 ) )

So what actually happened here in this formula is, it compared each row of the date with itself that is every row was considered as a “Minimum Date” for that particular product key. So basically, the formula at the back-end was evaluated like this -

'First Date Of Sales Product 214 = 
CALCULATE( [Min date sales table] , 
   Sales[ProductKey] = 214 )

But now, when you add the “ALL” function what it actually does is, it opened the entire window of the period i.e. from start date that is, minimum date till the end date that is, maximum date. So here not every row was considered as a “Min Date” like it was doing earlier. I’m also providing few of the links below for the reference from the education portal that will help you understand the concept in a much more better manner.

Hoping you find this useful and helps you to understand the subtle difference between the two. :slightly_smiling_face:

Thanks and Warm Regards,

Hi @Pbovet, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Thank you for your quick reply ,very appreciated!