Forum Discussion

rachelbi's avatar
rachelbi
Icon for Helper I rankHelper I
2 years ago

Create two date field relationship with date table for slicer

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...
Need Help
_AAndrade's avatar
_AAndrade
Icon for Resident Rockstar rankResident Rockstar
2 years ago

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's avatar
rachelbi
Icon for Helper I rankHelper I
2 years ago

I have made the inactive relationship and below change but still showing blank ...

 

...

RETURN
CALCULATE(
    DISTINCTCOUNT( Assessments[Assessment Title] ),
    FILTER(
        Assessments,
        Assessments[Site Name.lookupId] IN SelectedSites),
        USERELATIONSHIP(Assessments[Date Commenced],'Date'[Date])
   
)
  • _AAndrade's avatar
    _AAndrade
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Try this:

    CALCULATE(
        DISTINCTCOUNT( Assessments[Assessment Title] ),
        FILTER(
            ALL(Assessments),
            Assessments[Site Name.lookupId] IN SelectedSites)
          ),
    USERELATIONSHIP(Assessments[Date Commenced],'Date'[Date])
       
    )