Forum Discussion
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!
Hi Naomig2088
I would like to apologize for the belated reply.
I modified my sample.
Date table:
Employee table:
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.
The role of the slicer is to filter out the previous 12 months or the previous 6 months of data.
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 _YuliaxIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
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:
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!
Hi Naomig2088
My sample:
1. Create a measure as follows and put measure into card visual.
count = COUNT('Table'[employee])2. Make the following settings for card visual.
You can also try the following:
1. Create a slicer table as shown in the screenshot.
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])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.
Hi Naomig2088
I would like to apologize for the belated reply.
I modified my sample.
Date table:
Employee table:
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.
The role of the slicer is to filter out the previous 12 months or the previous 6 months of data.
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 _YuliaxIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
