topic Re: Measure calculate function with few filters in DAX Commands and Tips

Measure calculate function with few filters

IlyaF — Wed, 17 Aug 2022 09:19:00 GMT

Hi Community,

I have the following measure:

Abandoned = calculate(
count(
'Apps & Grants'[Grant ID]),
'Apps & Grants'[Current State]="Abandoned"||
'Apps & Grants'[Current State]="Withdrawn" ||
'Apps & Grants'[Current State]="Declined" ||
'Apps & Grants'[Current State]="To Delete"

)

Now I would like to add another filter with a date, to calculate the same measure only for the current year, based on the 'TimeStamp' column that I have in the table. So I tried the following:

Abandoned = calculate(
count(
'Apps & Grants'[Grant ID]),
('Apps & Grants'[Current State]="Abandoned"||
'Apps & Grants'[Current State]="Withdrawn" ||
'Apps & Grants'[Current State]="Declined" ||
'Apps & Grants'[Current State]="To Delete"),

'Apps & Grants'[Timestamp Entered State]=YEAR(TODAY())

)

But it seems to be wrong as I am getting the same result 😞
What am I doing wrong?

Re: Measure calculate function with few filters

lbendlin — Sun, 21 Aug 2022 22:23:11 GMT

No, that looks syntactically correct. Check your data - maybe your timestamp is not a numeric year?

By the way you can slightly simplify your filter

Abandoned = calculate( count('Apps & Grants'[Grant ID]), 'Apps & Grants'[Current State] IN {"Abandoned","Withdrawn",Declined","To Delete"}, 'Apps & Grants'[Timestamp Entered State]=YEAR(TODAY()) )

Re: Measure calculate function with few filters

IlyaF — Tue, 30 Aug 2022 06:30:20 GMT

Hi lbendlin, thanks for your reply.

My timestamp field is in a Short Date format:

And still, when filtering only the 'Current State' field, the result is fine (79).
But when adding the 'Timestamp' field filter, I get a Blank result while when filtering the data itself manually, the result should be 10.

Re: Measure calculate function with few filters

AnthonyJoseph — Tue, 30 Aug 2022 14:53:46 GMT

Hi IlyaF ,

If you want to filter by the current year then you will have to surround the column (Timestamp Entered State) by year function so the updated measure will be like:

Abandoned_new = calculate( count( 'Apps & Grants'[Grant ID]), ('Apps & Grants'[Current State]="Abandoned"|| 'Apps & Grants'[Current State]="Withdrawn" || 'Apps & Grants'[Current State]="Declined" || 'Apps & Grants'[Current State]="To Delete"), Year('Apps & Grants'[Timestamp Entered State])= YEAR(TODAY()) )

Hope this helps

Thanks,

AnthonyJoseph

Re: Measure calculate function with few filters

IlyaF — Wed, 31 Aug 2022 05:30:10 GMT

Hi AnthonyJoseph,

Thanks! This helped 🙂