cancel
Showing results for
Did you mean:
w2zahn 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

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.

3 REPLIES 3 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

## 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. 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.