Forum Discussion

Contezini's avatar
Contezini
Frequent Visitor
5 years ago
Solved

Relationship from Calender Table with Table with Start and End Date

Dear Community,   I am having an issue which I believe it might be something common, since I already found a lot of similar topics searching for this topic through the community. But sadly, I could...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    Since the incidents can span multiple dates and you only have one row for each incident, creating a relationship to the calendar table isn't going to work in a very useful way. Note that trying to put a calculated column with "active"/"inactive" on the incident table is doomed to failure since a calculated column cannot read slicer values since it is only evaluated once per data load (not in response to user interactions).

     

    A measure can read slicer values, so you can write one to count incidents on a particular date (to use in your chart) along these lines:

     

    IncidentCountOnDate =
VAR xDate = SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            Incidents,
            Incidents[Start Date] <= xDate
                && Incidents[Finish Date] >= xDate
        )
    )

     

    Filtering incidents for a month is more difficult than for a day but your Check_If_Active seems to be on the right track (as a measure, not a calculated column) if it's filtering a visual.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

Since the incidents can span multiple dates and you only have one row for each incident, creating a relationship to the calendar table isn't going to work in a very useful way. Note that trying to put a calculated column with "active"/"inactive" on the incident table is doomed to failure since a calculated column cannot read slicer values since it is only evaluated once per data load (not in response to user interactions).

 

A measure can read slicer values, so you can write one to count incidents on a particular date (to use in your chart) along these lines:

 

IncidentCountOnDate =
VAR xDate = SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            Incidents,
            Incidents[Start Date] <= xDate
                && Incidents[Finish Date] >= xDate
        )
    )

 

Filtering incidents for a month is more difficult than for a day but your Check_If_Active seems to be on the right track (as a measure, not a calculated column) if it's filtering a visual.

  • Contezini's avatar
    Contezini
    Frequent Visitor
    5 years ago

    Thank you for your answer Alexis!

     

    The active count measure worked, thank you!

     

    At first instance, it wasn't working. Later on I realized I had another table with "both" relation which was the reason for the measure to not be working....

    The measure I was trying to create is not even needed anymore:

    I just add on "Filters for this visual" this measure you suggested to be greater then 0, and it automatically filters only the active ones in the selected period.

     

    PS: I did a small change in the measure you sent to englobe a bigger period (like selecting multiple months)

    That's how the measure looked like in the end:

     

    3.0 Active Incidents = 
VAR xBeginDate = (FIRSTDATE( 'Calender'[Date] ))
VAR xEndDate = (LASTDATE( 'Calender'[Date] ))
VAR xCount = 
    COUNTROWS (
        FILTER (
            Incidents,
            Incidents[Start Date] <= xEndDate
            && (Incidents[Finish Date] >= xBeginDate || ISBLANK(Incidents[Finish Date]))
        )
    )
RETURN