Reply
mickey
Frequent Visitor
Partially syndicated - Outbound

How to keep existing filters while using calculate

Hi,

 

I have a table A which has the following fields: Episode, Start Date End Date

I have created another calculated table B which has a list of dates and I am trying to pull from table A how many episodes have a start date <= date in table B and a end date > date in table B.

 

I tried the following calculation:

Count =

VAR TableADate = 'TableA'[Date]
RETURN
    CALCULATE(
        COUNTROWS('TableA'),
        FILTER('TableA',
        'TableA'[Start Date] <= TableA
        && 'TableA'[End Date] > TableA     
    )
 
Though the above calculation is not working with a slicer I have for Table A. 
 
Thanks for the help.
2 REPLIES 2
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

Hi,

You should be writing a measure (not a calculated table formula).  Share some data in a format that can be pasted in an MS Excel file and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

Syndicated - Outbound

@mickey , A slicer value can not be used in a calculated table

 

Consider these measure ways

 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)