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

Thanks _AAndrade  for your reply. I tried but don't know where to put the USERRELATIONSHIP() function.

 

.....

RETURN
CALCULATE(
    DISTINCTCOUNT( Assessments[Assessment Title] ),
    FILTER(
        Assessments,
        Assessments[Site Name.lookupId] IN SelectedSites
    ),
DATESBETWEEN(Assessments[Date Commenced],'Measure'[Start Date],'Measure'[EndDate])
)
  • _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])
           
        )