Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PaulDBrown
Community Champion
Community Champion

Minimum Date: ALL does not ignore date filter context

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:model.PNG

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:

 

matrix.PNG

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

Thanks,
Paul.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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.

 

Solution.jpg

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

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.

 

Solution.jpg

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@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? 

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

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.