Minimum Date: ALL does not ignore date filter context

[PaulDBrown]

Good evening from Spain!

I am hoping to get some advice since I am stuck at a problem and can't seem to find a solution. I have a very simple model consisting of a fact table (emails sent: date sent, lead, category...), a calendar table and a Leads lookup table (referenced to the fact table.
This is a screenshot of the model setup:

What I'm trying to achieve is the minimum date a lead was emailed. This is simple enough against a lead, but what I'm after is to identify which month the lead was first emailed (in order to then count how many leads were first emailed by month). I have set up a matrix with (from calendar) Year, Month Name and (from Leads table) Lead. I have tried a number of measures but none return the minimum date for each lead regardless of the Year and Month context. I would have thought that a simple Calculate with an ALL filter would do, but these do not ignore the Year and Month context in the matrix. Here is a screenshot:

Please help! I've been pulling my hair out on this for a couple of days!

Thanks,
Paul.

[PaulDBrown · Accepted solution]

Hi all,

Just to help if anyone is experiencing this same problem, I have found a solution by reading the following article by Alberto Ferrari on sqlbi.com:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

Apprently I needed to add CALCULATETABLE to the filter in order to obtain the first date regardless of the date filter in the matrix.

I obviously still have a long way to go to understand the ALL function...

[JDanielHarvey]

@PaulDBrown Wonder if you had used the ALL() funciton on your fact table (ex. ALL(Mail) ) rather than the 'Mail'[Date sent] column if that would have removed the filters from the Calendar table since you would be referrencing the expanded table in the data model. Assuming you are using the Calendar table in the table you have listed but either way I believe that would also have worked. 

I read the article but I'm not really clear why the CALCULATETABLE would have worked on a single column. I understand that ALL() inside CALCULATE() should really be thought of as REMOVEFILTERS() similar to KEEPFILTERS() (inverse of course). Also curious if FILTER(ALL() would have worked. 

In your screenshot, is ABC, 123 and ZZZ folly examples of leads? 

[v-juanli-msft]

Hi @PaulDBrown 

Some references for you to learn ALL function further.

Video: How to Use The ALL function in Power BI & DAX

This article provides a complete explanation of the behavior of the ALLxxx functions in DAX. When used as filters in CALCULATE, ALLxxx functions might display unexpected behaviors.

Best Regards
Maggie

[PaulDBrown]

Hi Maggie,

Thank you very much for providing the resources, I'll take a deep dive and hope to get a better understanding of how ALL works,

Best regards,

Paul