Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.