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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Contezini
Frequent Visitor

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 couldn't find a solution that fits to my problem.

 

I have two Data Tables and two Head Tables:

  1. Site Production Data: Table containing Site ID, Month and Energy production from that month
  2. Incidents: Table contaning all the incidents: Site ID, Comment, Start Date and End Date
  3. Site Info: Table contaning info from each site (address, etc)
  4. Calender: An =calenderauto() table to use unique filter for Date (in daily basis)

(please see attached xlsx  to see a sample of the data used)

https://drive.google.com/file/d/1CGfA3yFGci3GuOx7bkFi912pHPW08SGE/view?usp=sharing 

 

On Power BI I want the user to be able to select an specific Site and Month, and then he should see:

  • The Production for that Month
  • All the Incidents that happened in between that month (in a table, for example)

 

My Problem is:

  • How should I relate the dates? I tried with Calender and StartDate, but obviously that is bad, because when I filter "February", I don't get the Incidents that started in January and where only finished in February (or even in March)

A solution I tried:

  1. Cancelled the relation between Calender and Incidents
  2. Create a measured called "Check_If_Active", which should check if the incident was active during the filtered month

 

 

 

 

 

VAR xFirstPeriod= CALCULATE(FIRSTDATE(Calender[Date].[Date]),ALLSELECTED(Calender))
VAR xLastPeriod= LASTDATE(Calender[Date].[Date])

VAR xStartDate = CALCULATE(MIN(Incidents[Start Date]))
VAR xFinishDate= CALCULATE(MIN(Incidents[Finish Date])
)
RETURN
    IF(
        AND(
            xStartDate  <=xLastPeriod,
            OR(ISBLANK(xFinishDate), xFinishDate >= xFirstPeriod)
        ),
        "Active",
        "Inactive"
    )​

 

 

 

 

The idea was to put that measure in the Incidents Table and filter only the "Active". I tried changing it with ALL, ALLSELECTED, FIRSTDATE, etc. but not a single variation I tried worked.

 

 

Can someone guide me through how should I Proceed in this case?

 

Extra:

I would also like to make a line chart: on X axis, the Days and on Y Axis the count of Active Incidents

For example: If I have the two following Incidents:

Inc1 - Start Date 01/03/20 - Finish Date 04/03/20

Inc2 - Start Date 03/03/20 - Finish Date 05/03/20

The chart would count 2 for 3rd and 4th, and 1 for 1st, 2nd and 5th of march.

 

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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.

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors