Exclusions...

[LearnToFly]

Okay so I have a table of exclusion data such as outages, extreme downtimes like weather days, etc.  My plan was to create a flag that denotes the date when an exclusion occured (done).  Then calculate use the start and stop time in the table to exclude that time frame from the normal Start and End Time for our call data.  However, I am having trouble having dax calculate it...here is my code and the error: 

If_Exclusion =

IF (

    'RollingCalendar'[Is_WorkDay] = "exclusion",

    // If Is_WorkDay is "exclusion", perform time-related calculations here

    // For example, subtract CE_EndTime from CE_StartTime and subtract the result from Time_StopTime

    'Call Detail Record Call Legs Simplified'[Time_StopTime] - ('CE_Exclusions'[CE_EndTime] - 'CE_Exclusions'[CE_StartTime]),

    // If Is_WorkDay is not "exclusion", return a default value or perform a different time-related calculation

    // For example, you might want to return the original Time_StopTime

    'Call Detail Record Call Legs Simplified'[Time_StopTime]

).  Howeer it states that Dax is saying that the column contains many values without specifiying an aggregation such as min, max, count, etc.  All columns have a Long Time Stamp format and the tables are related correctly for all of my other flags...Any suggestions?

[Anonymous]

Hi @LearnToFly ,

Can you give us your sample data and expected results? Thank you so much.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LearnToFly]

Okay so here is some sample data I have a table that collects call data by call id, it has a start time, stop time, leg id, and call time.  I just figured out that I have to collect the call time for each leg to get the entire call duration, which can change depending on the number of legs.  Then figure out how to subtract the outage time from that.    My exclusion table is simple its a description of type of outage, weather, etc. with a start and stop time.  Which is going to require me to figure out the extent of the exclusion.  What do you suggest for collecting sums of varying call leg data?  Call ID is typically auto assigned, Leg ID...assigns a number for each leg of the call 1-operator, 2-attendant, 3-transfer, 4-voicemail, etc.  Start and stop time are long format, however because Call Time is in short format I created two fields that separate the time out from the date called Time_StartTime and Time_EndTime.  I hope this is descriptive enough. 😕 The short format is HH:MM:SS.