Forum Discussion

Naomig2088's avatar
Naomig2088
Icon for Helper II rankHelper II
2 years ago
Solved

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 cha...
@powerbi
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 _Yuliax

     

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

Naomig2088's avatar
Naomig2088
Icon for Helper II rankHelper II
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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 _Yuliax

 

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