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
IlyaF
Regular Visitor

Measure calculate function with few filters

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? 

1 ACCEPTED SOLUTION
AnthonyJoseph
Resolver III
Resolver III

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

View solution in original post

4 REPLIES 4
AnthonyJoseph
Resolver III
Resolver III

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

Hi AnthonyJoseph,

 

Thanks! This helped 🙂

lbendlin
Super User
Super User

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

 

 

Hi lbendlin, thanks for your reply.

My timestamp field is in a Short Date format:

IlyaF_0-1661840568606.png

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.

IlyaF_1-1661840987563.png

 

 

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