Related Function in Direct Query with new calculated column

[w2zahn]

Hi masters,

That's my first complex calculation using conditions (if) for different fields.

I couldn't do one step which is check if the date is a holiday.

I'm struggling to do it in a measure so I decided to create new columns to help but looks like it could be done easily.

Let's go to the issue.

I have to calculate KPI but the rule change for many reasons.

First step, calculate how many minutes between two dates (I created a new column to do this || yes, I know that I don’t need a new column to get the information but because I need this information to use in the other calculations and I couldn’t create all of then in a measure, I break in steps to make easier but I’m still missing one bit)

Second step, I created a new column with the majority of the validation (missing check if the date is a holiday)

Here is the code:

Taa =
IF(RELATED(Customer[CustomerAA]) = "A86C00A6AED6",                                                  // Customer AA
   IF(1=1,                              -------- * -------                                                                        // Holiday
      IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 60,1,0),                                        // KPI on holidays
      IF(WEEKDAY(Job[BIPUDate]) = 1 || WEEKDAY(Job[BIPUDate]) = 7,                                    // KPI on weekends
         IF(Job[BIPUTIME] > 479 && Job[BIPUTIME] < 1020,                                                        // between 8 am to 5 pm
            IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 30, 1,0),
            IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 60, 1,0)),                                  // other time
            IF(Job[BIPUTIME] >= 360 && Job[BIPUTIME] <= 540,                                               // KPI on week days
              IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 15, 1,0),                                // between 6 am to 9 am
              IF(Job[BIPUTIME] >= 541 && Job[BIPUTIME] <= 959,
                IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 30, 1,0),                        // between 9:01 am to 3:59 pm
                   IF(Job[BIPUTIME] >= 960 && Job[BIPUTIME] <= 1140,
                     IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 15, 1,0),                     // between 4 pm to 7 pm
                        IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 60, 1,0)))))),             // other time
IF(RELATED(Customer[CustomerAA]) = "D8B42D6EA2C9",                                                   // Customer BB
    IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 45,1,0),
    IF(Job[M_Arrive_Time] > 0 && Job[M_Arrive_Time] <= 45,1,0)))                                       // Other customers

Last step, calculate the KPI (%) achieved on that day. (Easy when the others steps were done).

That's how is the mode

l

Here you can find the file to test: https://1drv.ms/f/s!An_2fMwp0zTYgmHLNQqgJ0xJo08E

There is a field in the table job called taa where you'll find the query

As you can see in the script above, where you find -----*------ is where I should test if the date (Job[BIPUDate]) is a holiday.

The datamodel has a table called Holiday where can be checked holidays.

Thanks in advance!!!

[v-lili6-msft]

hi, @w2zahn 

First step, calculate how many minutes between two dates (I created a new column to do this || yes, I know that I don’t need a new column to get the information but because I need this information to use in the other calculations and I couldn’t create all of then in a measure, I break in steps to make easier but I’m still missing one bit)

For calculate how many minutes between two dates, you could use DATEDIFF Function.

 What is your expected output?  could you please share some expected output based on this data?

Best Regards,

Lin

[w2zahn]

Hi Lin,

For calculate how many minutes between two dates, you could use DATEDIFF Function.

Thanks for you tip, I'm using this fuction. You can find in the example linked in my first message.

What is your expected output?  could you please share some expected output based on this data?

The first goal is to validate if the date of the job was a holiday. As I explained in my first message. I couldn't find a way to do all the validations in one step as a measure.

The main idea is track the KPI of performance. That's why I do have the counters and gauge as showed in the example linked in the first message.

Thanks for your attention.

[v-lili6-msft]

hi, @w2zahn 

You could try to add this conditional in a measure.

Filter( "Job", Job[BIPUDate] IN VALUES( Holidays[Date])) 

use this to validate if the date of the job was a holiday.

Best Regards,

Lin