Multi Conditional Logic

[bhmiller89]

I'm trying to determine if a day is going to be considered "billable."  Basically, if a day is a week day AND it is not one of 7 holidays.

I made a Date Table as well as a Holiday Table that only lists the 7 holidays.  This is what I came up with, but I'm probably way off

[Anonymous]

You've set one condition as IF(DateTable[FullDate] <> RELATEDTABLE("An entire table rather than a single corresponding row in a single column in that table"). There's no logical way to resolve that condition. What column do you want to compare [FullDate] to?

[Anonymous]

@bhmiller89 yeah I was just in the middle of writing another reply warning you that error would happen. I think you intended to do an OR comparison between two conditions and return "yes" if either condition is met, but what you've actually done is written an OR comparison between two unrelated IF statements that both return text rather than a boolean value, and since text isn't a boolean value you're just comparing two errors. Your formula as written actually says "IF the text returned by condition 1 is a boolean TRUE value or the non-boolean text returned by condition 2 is a boolean TRUE, return TRUE, else return FALSE. It gets as far as "IF the text returned by condition 1 is a boolean TRUE and throws an error because that's impossible. Your formula should look like...

IsBillableDay = IF(DateTable[DayofWeek] < 6 || DateTable[FullDate] <> RELATED(OtherTable[ColumnName]), "Yes", "No")

[bhmiller89]

That makes sense, I'm getting close.  I actually need a formula that meets both conditions, not either.

I assigned days of the week numerical values with Saturday and Sunday being 6 and 7, respectively.  So in order for a date to be billable it must be a weekday (1-5) AND not one of the holidays

[Anonymous]

Yeah, I was just wondering why you were doing an OR comparison. the AND operator is &&.

I suspect you will still have problems though. I haven't seen the structure of your Holidays table but I can't imagine any way of setting up a holiday calendar and a relationship between it and a date table that would work with the formula as written. The two tables are related by dates, right? DateTable[FullDate] is related to Holidays[FullDate] or something like that? And I'm guessing that Holidays only contains dates that are holidays, so July 11, 2016 is probably not on the Holidays table at all, right? So you would need a condition that checks to see if the date even exists in the other table, not whether they are equal. So maybe something like...

'If the day of week is less than 6 and a search of Holidays returns blank for this date, then "Yes" else "No".'

IsBillableDay = IF(DateTable[DayofWeek] < 6 && ISBLANK(LOOKUPVALUE(Holidays[HolidayName], Holidays[FullDate], DateTable[FullDate])), "Yes", "No")

[bhmiller89]

Now that I have a column that tells me whether or not a day is considered "billable" I am trying to create a measure for "How many billable hours in a month"

So basically show each month for 2016 with number of billable hours (8 X #billable days per month) but I can't figure out how to do this without getting a ton of errors.

[Anonymous]

@bhmiller89 you could write a SUMX that's specific to months but I find it easier to leave things flexible.

Billable Hrs = 8 * CALCULATE( DISTINCTCOUNT(DateTable[Date]), FILTER( DateTable, DateTable[IsBillableDay] = "Yes"))

That should give you the number of billable hours for any given period. If you use Month of Year as the row context it will give you billable hours per month, if you use year it will give you the full year, quarter, week, random filtered cluster of 17 days, etc.

[bhmiller89]

Excellent! For 2016 for some reason it's only showing Jan-Jun for 2016.  All previous years shows every month.

My next step is to calculate pre-scheduled PTO which is any SchDurAdj entered under a specific job number.  I didn't get any errors when I wrote this measure but I'm also not getting any data to populate in the visuals

PTOhrs= CALCULATE(SUM('SR Calendar' [SchDurAdj]), FILTER('SR Calendar'[JobNum] = "2016-DAT-J03"))

This is the last one, promise!

[v-qiuyu-msft]

Hi @bhmiller89,

Which visual did you place this measure "PTOhrs" in? Does the [SchDurAdj] contains values when the [JobNum] = "2016-DAT-J03". Please drag this measure to a Card visual, check if any value displays. If issue persists, please provide more information about [SchDurAdj] and [JobNum] for our analysis.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

I don't know how this table is structured or how those columns work, so it's hard to say. What is in the SchDurAdj column?

[bhmiller89]

Wow I feel dumb.  I changed the || to && and now it works.  Thank you so much for pointing me in the right directioN!

[bhmiller89]

I changed the related table to just the related column in question and I confirmed the relationships were set up.  Now I'm getting this: