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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JustinDoh1
Post Prodigy
Post Prodigy

Calculate function - filter

I am trying to understand this DAX code here.

 

DAYSINMONTH =
               day(calculate(
                                       ENDOFMONTH('Calendar'[Date]),
                                       Table1
                                    )
                       )
 

Why do we have Table1 here?

I thought extra line is for filter (or extra filters), but this is only table name.

 

There is a relationship between 'Calendar' table and 'Table1' table.

 

Update:

I have attached Pbix file for better illustration.

Sorry for confusions.

 

Thanks.

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

I don't think the post initiator, so far, wraps his/her head around my solution in another post of his/hers.

Solved: How to reference a value on related table - Microsoft Power BI Community

 

CALCULATE filters are actually tables.

Indeed, this is how filters works in DAX. It's an explicit leveraging of Expanded Table in such statements, CALCULATE( XXX, Table ).


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

16 REPLIES 16
tamerj1
Super User
Super User

CALCULATE filters are actually tables. As per your data model the Calendar table filters the Table1. But in the code you mentioned, Table1 filters the Caledar date column. 
The difference is that without using Table1 as a filter the code will return all the dates in the Calendar table. While using when using Table1 as a filter then all the months that are missing from Table1 will return blank. It seems that the user does not want to see the number of days for those months. 

CNENFRNL
Community Champion
Community Champion

I don't think the post initiator, so far, wraps his/her head around my solution in another post of his/hers.

Solved: How to reference a value on related table - Microsoft Power BI Community

 

CALCULATE filters are actually tables.

Indeed, this is how filters works in DAX. It's an explicit leveraging of Expanded Table in such statements, CALCULATE( XXX, Table ).


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL Sorry. I should have posted my follow up question to your response on other post. I guess the answer is "it's an explicit leveraging of Expanded Table in such statements". Thank you!

@tamerj1 this is not accurate.  The Calendar table would be filtered by any dates in filter context, which would in turn filter any dates in Table1.

 

But the discussion is irrelevant because using CALCULATE is over-complicating the solution in the first place.  DAY(EOMONTH('Calendar'[Date], 0)) solves the problem.  More simply, ENDOFMONTH solves the problem.

@littlemojopuppy 

The dates in filter context are actually and should be from the date table. Therfore, when I write a measure that returns the last day of a month and I place it in a matrix visual for example and I slice by date from the date table, I will have to see all the dates in the raws of my matrix even if they do not exist in my fact table. The idea is to restric this date filter to only the dates that exist in the fact table or alternatively return blank in case othe measures that forces all dates to appear do exist in the same matrix visual. 

Funny discussion, I'm wondering whether you ever ask youself a simplest but fairly intuitive question, what's the point of existence of ENDOFMONTH since there already exists EOMONTH?


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

littlemojopuppy
Community Champion
Community Champion

Hi @JustinDoh1 what is Table1?

I would think that just DAY(EOMONTH('Calendar'[Date], 0)) would be enough?

@littlemojopuppy 

Table1 is a name of a table.

JustinDoh1_1-1646530079252.png

I am getting error mesage.

 

 

Update:
I have updated the Pbix file, and bottom are illustrations of two cases:

JustinDoh1_0-1646615499319.png

JustinDoh1_1-1646615622108.png

 

@JustinDoh1 

As I said in my previous reply, the card here mentions the number of days of the last month available in Table1. If the last month in Table1 is September then you would see 30. But wihout having Table1 in the filter of your code then you would allways see 31 with is the number of days of December. 

The error in the other formula is irrelevant. You are placing 0 which means FALSE as a CALCULATE filter table which is not allowed. 

@tamerj1 if you paid a little bit of attention, you'd notice that @JustinDoh1 is a Super User and I'm fairly certain they understand how the CALCULATE function works.  You'd also have noticed that what I suggested did not involve the CALCULATE function...simply DAY(EOMONTH('Calendar'[Date], 0)).

@littlemojopuppy 

Sorry for all confusions that were created.

I appologize it. I have attached Pbix file on the post now.

This is what I see:

JustinDoh1_0-1646615061736.png

JustinDoh1_1-1646615220935.png

What am I missing?

Thanks!

Hi @JustinDoh1 no worries at all!  I think you're the one person who didn't create confusion.  🙂

@littlemojopuppy 
Your code is 100% correct. I wrote absoluteley nothing against it. 
And yes I know that you are both Super Users but what does that have to do with me expressing my openion in asubject?

 

@tamerj1 I never said anything about my own status as a Super User, only that JustinDoh1 is one.  And because they are one, I'm certain they understand using the CALCULATE function.

 

And if my code is 100% correct, why do you feel compelled to provide another answer to it?  All you're doing is creating confusion for users who see multiple responses.  When apparently a question has been answered correctly.

@littlemojopuppy 

It seems you did not read my reply to hIm:

CALCULATE filters are actually tables. As per your data model the Calendar table filters the Table1. But in the code you mentioned, Table1 filters the Caledar date column. 
The difference is that without using Table1 as a filter the code will return all the dates in the Calendar table. While using when using Table1 as a filter then all the months that are missing from Table1 will return blank. It seems that the user does not want to see the number of days for those months. 

I might be wrong. But that is my openion. 

 

@JustinDoh1  I assumed it was a table.  Whole thing seems unnecessary.

 

I didn't use ENDOFMONTH.  I used EOMONTH (returns the last day of a month with offset by number of months).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors