topic Re: Calculate function - filter in DAX Commands and Tips

Calculate function - filter

JustinDoh1 — Mon, 07 Mar 2022 01:16:18 GMT

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.

Re: Calculate function - filter

littlemojopuppy — Sun, 06 Mar 2022 00:57:59 GMT

Hi JustinDoh1 what is Table1?

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

Re: Calculate function - filter

JustinDoh1 — Mon, 07 Mar 2022 01:17:52 GMT

littlemojopuppy

Table1 is a name of a table.

I am getting error mesage.

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

Re: Calculate function - filter

littlemojopuppy — Sun, 06 Mar 2022 01:29:55 GMT

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).

Re: Calculate function - filter

tamerj1 — Sun, 06 Mar 2022 09:24:45 GMT

JustinDoh1

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.

Re: Calculate function - filter

tamerj1 — Sun, 06 Mar 2022 10:11:55 GMT

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.

Re: Calculate function - filter

littlemojopuppy — Sun, 06 Mar 2022 11:35:20 GMT

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)).

Re: Calculate function - filter

littlemojopuppy — Sun, 06 Mar 2022 11:39:23 GMT

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.

Re: Calculate function - filter

tamerj1 — Sun, 06 Mar 2022 12:23:34 GMT

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?

Re: Calculate function - filter

littlemojopuppy — Sun, 06 Mar 2022 12:27:37 GMT

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.

Re: Calculate function - filter

tamerj1 — Sun, 06 Mar 2022 12:47:52 GMT

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.

Re: Calculate function - filter

CNENFRNL — Sun, 06 Mar 2022 19:50:39 GMT

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?

Re: Calculate function - filter

CNENFRNL — Sun, 06 Mar 2022 20:09:36 GMT

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 ).

Re: Calculate function - filter

tamerj1 — Sun, 06 Mar 2022 20:55:45 GMT

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.

Re: Calculate function - filter

JustinDoh1 — Mon, 07 Mar 2022 00:50:36 GMT

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!

Re: Calculate function - filter

JustinDoh1 — Mon, 07 Mar 2022 01:07:46 GMT

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:

What am I missing?

Thanks!

Re: Calculate function - filter

littlemojopuppy — Mon, 07 Mar 2022 01:51:02 GMT

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