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
Anonymous
Not applicable

How to count rows filtered by unrelated table

Hi there!

I have two unrelated tables:
1. Calendar table, single date
2. Project table, two dates: start date, end date.

I use a date slicer, and I want to count how many days the project period covers during that time frame.

Macro_0-1648733960461.png


I tried many measures, such as:

 

 

Days = 
CALCULATE(
DISTINCTCOUNT('Calendar'[Date]),
        'Job'[Start] <= MAX('Calendar'[Date]),
        'Job'[End] >= MIN('Calendar'[Date])
    
)

 

 

In the example, it should be 4 (because 4 days in Feb 2020) but I only get 29, it counts the whole month. I keep searching everywhere for a solution but I can't find any....

Please help me! 🙂

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

How about this?

Days =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] >= MAX ( Job[Start] )
            && 'Calendar'[Date] <= MAX ( Job[End] )
    )
)

days.gif

 

 

Best Regards,

Icey

 

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

2 REPLIES 2
Icey
Community Support
Community Support

Hi @Anonymous ,

 

How about this?

Days =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] >= MAX ( Job[Start] )
            && 'Calendar'[Date] <= MAX ( Job[End] )
    )
)

days.gif

 

 

Best Regards,

Icey

 

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

Anonymous
Not applicable

I fixed the days calculation like this:


I created two extra measures:

 

 

JobMin = 
VAR JobMin = CALCULATE(
    MINX('Job',[Start]),
        'Job'[Start] <= MAX('Calendar'[Date]),
        'Job'[End] >= MIN('Calendar'[Date])
)

RETURN 

if( 
NOT ISBLANK(JobMin) && JobMin <MIN('Calendar'[Date]), 
    MIN('Calendar'[Date]), 
    JobMin
)

 

 


and 


 

 

JobMax = 
VAR JobMax = CALCULATE(
    MAXX('Job',[End]),
        'Job'[Start] <= MAX('Calendar'[Date]),
        'Job'[End] >= MIN('Calendar'[Date])
)

RETURN 

if( 
NOT ISBLANK(JobMax) && JobMax >MAX('Calendar'[Date]), 
    MAX('Calendar'[Date]), 
    JobMAX
)

 

 


And so I fixed the days calculation like this:

 

 

Days = 

Var MinJob = [JobMin]
VAR MaxJob = [JobMax]

Var MaxDate = MAX('Calendar'[Date])
var MinDate = MIN('Calendar'[Date])

RETURN 

CALCULATE(
    SUMX('Job',DATEDIFF(if('Job'[Start]<MinJob,MinJob,'Job'[Start]), if('Job'[End]>MaxJob, MaxJob, 'Job'[End]), DAY) +1),
    'Job'[Start] <= MaxDate,
    'Job'[End] >= MinDate
    
)

 

 


Result:

Macro_0-1648744361206.png


(ps: current utilization = quantity x days)


Still checking all numbers...

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.