0

I am trying to get data insights of my calendar through visualizations in PowerBI. I am able to get almost all data from my outlook calendar using in-house API in PowerBI. I intend to find how many conflicting meetings I have per week, but I couldn't find any flag column for that. I'm trying to use time slicers to generate a what-if parameter to calculate a flag, but it doesn't work. Is there any way I can track conflicting meetings?

The data I have relative to meetings is as below -

All the data i have pulled from the exchange server is here

enter image description here

Sushruth
  • 43
  • 8

1 Answers1

1

You could add a Calculated Column to the dataset, with a formula like this:

Conflicting = 
VAR StartDate = 'Calendar'[Start]
VAR EndDate = 'Calendar'[End]
VAR IDCurrent= 'Calendar'[Id]
RETURN
IF ( 
COUNTROWS( 
    FILTER (
        ALL('Calendar');
        'Calendar'[Start] < EndDate &&
        'Calendar'[End] > StartDate &&
        'Calendar'[Id] <> IDCurrent
    )
) > 0; TRUE(); FALSE())

This formula checkes if there are different rows within the same date range.

You can adjust the date comparions based on your needs. I've got the logic from this post and removed the equal signs, to prevent contiguous items marked as overlapping.

The Id column is the Unique Identifier (like a unique, primairy key) automaticly provided by Exchange Online. The filter on Id <> IDCurrent makes sure you're not mark the current row as overlapping, e.g. it searches for all rows exept the current one.:

ID Column Dataset

Result:

Outlook PowerBI

Edit: The formula above results in a true/false value. You can easily remove the if statement, to count the conflicting appointements, but remember that the value will be counted twice (or more); for each conflicting appointment.

TJ_
  • 629
  • 6
  • 12
  • Thanks, that's exactly what I'm looking for. I needed a flag value like your code provides me. I just have a small doubt. Does the "ID" in the code refer to the subject in the tr – Sushruth Mar 31 '18 at 16:13
  • 1
    No, the id was the Unique identifier I got back with the data from Exchange Online. Don’t you have it? Than maybe you should create your own key, with datetime + subject for example. – TJ_ Mar 31 '18 at 20:08
  • 1
    If you're referring to the last column of the data we get from exchange, then you're right. I already have it. Thanks for the heads up. – Sushruth Apr 01 '18 at 00:47
  • 1
    Yes, the Id column is indeed the latest column in the dataset. I'll add it to the answer. Gook luck! – TJ_ Apr 01 '18 at 13:09