cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
w2zahn Frequent Visitor
Frequent Visitor

Related Function in Direct Query with new calculated column

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

ltempsnip.png

 

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!!!

3 REPLIES 3
Community Support Team
Community Support Team

Re: Related Function in Direct Query with new calculated column

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

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
w2zahn Frequent Visitor
Frequent Visitor

Re: Related Function in Direct Query with new calculated column

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.

Community Support Team
Community Support Team

Re: Related Function in Direct Query with new calculated column

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

 

 

 

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