Two dates needing Filter with no relationship

[Anonymous]

Hi,

Could somebody please help me with the query below?

I'm trying to use a count filter on a start and end date referencing another table (BankHols) but I can't create a relationship with one because then there wouldnt be one with the other? I'm sure it would need a relationship unless I'm wrong?

CALCULATE(COUNT(RoomUtilisation[New Start Date]), FILTER(BankHols, BankHols[Date] >0)))),"0"

CALCULATE(COUNT(RoomUtilisation[New EndDate]), FILTER(BankHols, BankHols[Date] >0)))),"0"

Thanks

[amitchandak]

@Anonymous , refer how to deal with two dates

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Pragati11]

Hi @Anonymous ,

I don't understand the bit why you can't create the relationship in your statement below:

I'm trying to use a count filter on a start and end date referencing another table (BankHols) but I can't create a relationship with one because then there wouldnt be one with the other? I'm sure it would need a relationship unless I'm wrong?

Can you explain the highlighted statement please?

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 ,

I thought if it was related on the start date e.g. then it wouldnt function through the end date and vice versa?

Would it be fine for me to create just one relationship e.g. start date and still use my two count statements below?

Thanks

Liam

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Yes you can create a relationship on one of the common column, and still use other ones to filter in your calculations.

Try it, it will work.

Thanks,

Pragati