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
LeighMacKay
Helper I
Helper I

Create a count measure which work with filters

Hi All,

 

Can anybody advise how I create a measure which counts a date & time field?

If the date and time field does't exist (NULL) then don't count it. If the Date & Time field has any entry, other than null then = 1

I also need this COUNT to interact with filters on the report so the result changes based on what is selected.

 

Database name = eventminder_a_trinity

Date & Time field = created_at
Table Name = activity_log

I'm really struggling with it and i'm sure its simple once I see the code.  Unfortunately I am better at reading it than I am writing it at present.  But I am getting there by using tutorials and advice on these message boards.

 

Keep up the good work everybody!!!    Smiley Happy

1 ACCEPTED SOLUTION

Hi @LeighMacKay ,

 

It does work with filters. If you want to remove the impact of filters, you can use ALL in the formula like below.

 

Measure = calculate(count('table'[DateField]), all('table'[field]))

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @LeighMacKay ,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

The solution that you have responded with works.  But not in the nature which I require.

 

It doesn't work with the filter, but the total only at all times.

Hi @LeighMacKay ,

 

It does work with filters. If you want to remove the impact of filters, you can use ALL in the formula like below.

 

Measure = calculate(count('table'[DateField]), all('table'[field]))

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This now works.  Great stuff and thank you for your help

v-jiascu-msft
Employee
Employee

Hi @LeighMacKay ,

 

It seems you have many date fields. What would you like to show it? In a visual or as a column? The formula is simple like below.

 

Measure = count('table'[DateField])

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your reply.

 

The end goal is to have a CARD which COUNTS the TOTAL SUM of those selected.

Hi @LeighMacKay ,

 

Did you try the formula I posted in my last reply? It seems the solution in your scenario.

Measure = count('table'[DateField])

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.