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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Naomig2088
Helper II
Helper II

Count rows filtered table

Hi,

I have a table of employees which is filtered using a relative date (employee left employment in last 12 month). Is there a way to count based on this filter? Similarly, if the filter was to change to the last 6 months, for example, would the count function work dynamically? Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Naomig2088 

 

I would like to apologize for the belated reply.

 

I modified my sample.

Date table:

vxuxinyimsft_5-1705660697282.png

 

Employee table:

vxuxinyimsft_4-1705660597962.png

 

Then I ran the following test:

 

1. Create several measures as follow

Measure = IF(SELECTEDVALUE('Table'[Date]) >= MIN('Date'[Date]) && SELECTEDVALUE('Table'[Date]) <= MAX('Date'[Date]), 1, 0)
count = COUNTX(FILTER('Table', 'Table'[Date] >= MIN('Date'[Date]) && 'Table'[Date] <= MAX('Date'[Date])), 'Table'[employee])

 

2. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

vxuxinyimsft_8-1705661443046.png

 

The role of the slicer is to filter out the previous 12 months or the previous 6 months of data.

 

vxuxinyimsft_7-1705661408173.png

Is this the result you expect?

 

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Please remove any sensitive data in advance.

 

Best Regards,
Community Support Team _Yuliax

 

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

5 REPLIES 5
Anonymous
Not applicable

Hi @Naomig2088 

 

My sample:

vxuxinyimsft_0-1704448821837.png

 

1. Create a measure as follows and put measure into card visual.

count = COUNT('Table'[employee])

 

2. Make the following settings for card visual.

vxuxinyimsft_1-1704448960729.png

 

vxuxinyimsft_2-1704448991760.png

 

You can also try the following:

 

1. Create a slicer table as shown in the screenshot.

vxuxinyimsft_3-1704449059753.png

 

2. Create several measures as follow:

Measure = IF(SELECTEDVALUE('Table'[Date]) >= MIN('Date'[Date]) && SELECTEDVALUE('Table'[Date]) <= MAX('Table'[Date]), 1, 0)
count1 = COUNTX(FILTER('Table', [Measure] = 1), 'Table'[employee])

 

vxuxinyimsft_4-1704449160385.png

 

vxuxinyimsft_5-1704449181866.png

 

Is this the result you expect?

 

Best Regards,

Community Support Team _Yuliax

 

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

Thank you so much- this works for creating a dynamic count. The issue I'm having is that I'm trying to count at a point in time. My data shows employment start date and end date, but I need the outcome to be number of records at a point in time e.g. number of records as at 1st Jan 2024. Is this possible?

Thank you for your continued help.

Anonymous
Not applicable

Hi @Naomig2088 

 

I would like to apologize for the belated reply.

 

I modified my sample.

Date table:

vxuxinyimsft_5-1705660697282.png

 

Employee table:

vxuxinyimsft_4-1705660597962.png

 

Then I ran the following test:

 

1. Create several measures as follow

Measure = IF(SELECTEDVALUE('Table'[Date]) >= MIN('Date'[Date]) && SELECTEDVALUE('Table'[Date]) <= MAX('Date'[Date]), 1, 0)
count = COUNTX(FILTER('Table', 'Table'[Date] >= MIN('Date'[Date]) && 'Table'[Date] <= MAX('Date'[Date])), 'Table'[employee])

 

2. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

vxuxinyimsft_8-1705661443046.png

 

The role of the slicer is to filter out the previous 12 months or the previous 6 months of data.

 

vxuxinyimsft_7-1705661408173.png

Is this the result you expect?

 

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Please remove any sensitive data in advance.

 

Best Regards,
Community Support Team _Yuliax

 

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

MrBrownn
Frequent Visitor

Hello Naomie, 
If a understand correctly, 
You can dynamically count the employee left in the last 12 month by using a mesure 
 __Mesure =

CALCULATE (
       COUNT(IdEmployee)
       ,DATESINPERIOD(
             Table[Date],
            ,LASTDATE(Table[Date])
            ,-12, MONTH
        )
    )

            

Hi,

Thanks for your reply. I'm getting the following error message when I use the above measure:

Naomig2088_0-1704449207124.png

Here is the measure I've used:

Staff Count = CALCULATE(COUNT(Staff[EmploymentId]),DATESINPERIOD(Staff[EmploymentStartDate],LASTDATE(Staff[EmploymentStartDate]),-12,MONTH))
Can you please advise further?
Many thanks!

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.