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 September 15. Request your voucher.

Reply
Ducati_ST2
Frequent Visitor

USERELATIONSHIP works on three relationships between fact and dates table but not one specific date

I need your help with a problem I am having with USERELATIONSHIPS

 

I have a fact table linked to a date table and I have 5 related dates linked between the fact and date table.

 

I can get the USERELATIONSHIP to work on three of the inactive relationships but not with the end date relationship

 

For example This measure which is the active relationship works fine.

Total "Closed" Enquires = COUNTROWS (FILTER( Combined, Combined[Enquiry Status] = "Closed"))

 

This measure also works fine (leveraging an inactive relationship)

Total Enquiries Closed (Contact Start Date) = Calculate(COUNTROWS(Combined),Combined[Enquiry Status]="Closed", USERELATIONSHIP(Combined[Contact Start Date],Dates[Date]))

 

This measure also works fine(leveraging an inactive relationship)

Total Enquiries Closed (IGF Start Date) = Calculate(COUNTROWS(Combined),Combined[Enquiry Status]="Closed", USERELATIONSHIP(Combined[Enquiry Start Date (IGF)],Dates[Date]))

 

But this measure for some inexplicable reason will simply not work it just returns blanks on rows and a total which is correct.

 

Total Enquiries Closed (Enquiry End Date) = CALCULATE(COUNTROWS(Combined),Combined[Enquiry Status]="Closed", USERELATIONSHIP(Combined[Enquiry End Date],Dates[Date]))

 

I am at a complete loss why will some work but this particular measure will not work - what am I not seeing or understanding? Do I have too many relationships with the dates table? Please can you help me I have tried everything but I can not find a way to resolve this one 

1 ACCEPTED SOLUTION
Ducati_ST2
Frequent Visitor

Problem Solved
I traced the problem back to an incorrect data tye for the Enquiry End date it was a date/time data type instead of being just a date type
made the change and whola - it all works as it should 

Ducati_ST2_0-1651210552565.png

Andrew

View solution in original post

3 REPLIES 3
Ducati_ST2
Frequent Visitor

Problem Solved
I traced the problem back to an incorrect data tye for the Enquiry End date it was a date/time data type instead of being just a date type
made the change and whola - it all works as it should 

Ducati_ST2_0-1651210552565.png

Andrew

Ducati_ST2
Frequent Visitor

This is the output table for the above measures as you can see they are all working fine except the last one on the right with the Enquiry End date - its just returns blanks

 

Ducati_ST2_1-1651180729692.png

 

Ducati_ST2
Frequent Visitor

Ducati_ST2_0-1651180544197.png

This is the data model that I have at the moment. The active relationship is between Dates[Date] and Combined[Triage End date]

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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