Create two date field relationship with date table for slicer

Question

I have already had a "Date completed" relationship with Date table. What if I have another measure with "Date commenced" which also want to use the slicer to show dynamic figure. Is it possible? What shoud I do? Thanks

_aandrade · Answer

Hi&nbsp;rachelbi,You can create another relationship between Date Table and Assessments Table and put on inactive and then use the Function USERELATIONSHIP to activate that relationship for that specific measure.Take a look at this article: https://www.sqlbi.com/articles/using-userelationship-in-dax/

rachelbi · Answer

Thanks&nbsp;_AAndrade&nbsp; for your reply. I tried but don't know where to put the USERRELATIONSHIP() function.&nbsp;.....RETURNCALCULATE( &nbsp; &nbsp; DISTINCTCOUNT( Assessments[Assessment Title] ), &nbsp; &nbsp; FILTER( &nbsp; &nbsp; &nbsp; &nbsp; Assessments,&nbsp; &nbsp; &nbsp; &nbsp; Assessments[Site Name.lookupId] IN SelectedSites&nbsp; &nbsp; ),DATESBETWEEN(Assessments[Date Commenced],'Measure'[Start Date],'Measure'[EndDate]) )

_aandrade · Answer

Why you are using the assessments Date Commenced as a date table on DATESBETWEEN formula? You should use your date table.
The USERELATIONSHIP formula would be added on the filter of calculate function, but I didn't understand why you are using DATESBETWEEN formula.

rachelbi · Answer

I have made the inactive relationship and below change but still showing blank ...&nbsp;...RETURNCALCULATE( &nbsp; &nbsp; DISTINCTCOUNT( Assessments[Assessment Title] ),&nbsp; &nbsp; FILTER( &nbsp; &nbsp; &nbsp; &nbsp; Assessments,&nbsp; &nbsp; &nbsp; &nbsp; Assessments[Site Name.lookupId] IN SelectedSites),&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP(Assessments[Date Commenced],'Date'[Date])&nbsp; &nbsp; )

_aandrade · Answer

Try this:
CALCULATE(
&nbsp; &nbsp;&nbsp;DISTINCTCOUNT(&nbsp;Assessments[Assessment Title]&nbsp;),
&nbsp; &nbsp;&nbsp;FILTER(
&nbsp; &nbsp; &nbsp; &nbsp; ALL(Assessments),
&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Assessments[Site Name.lookupId]&nbsp;IN&nbsp;SelectedSites)&nbsp; &nbsp; &nbsp; ),USERELATIONSHIP(Assessments[Date Commenced],'Date'[Date])
&nbsp; &nbsp;
)

Forum Discussion

Create two date field relationship with date table for slicer

5 Replies

Related content

Creating relationships

Slicer Relationship

Relationship

Relationship/Slicer Issue

Create relationship dialog box