topic Re: How to check if one value of a combination of values has changed over time in DAX Commands and Tips

How to check if one value of a combination of values has changed over time

Anonymous — Mon, 13 Jun 2022 12:19:25 GMT

Hi Everyone!

I have a question for my fellow BI people. I'm gonna use dummy data to give an idea of our Power BI dataset:

So we have a Fact table Invoices with many columns, but the most important ones for my questions are:

Invoice Number
Worker Key
Customer Key
Date
IsPayroll?

To give you a visual representation:

My company wants a new column (based on a condition), that has 3 options; SET payroll, NO payroll, VARIABLE payroll.

They wanna know if a certain combination, for example above invoice nr 1 with customer key 20 and worker key 55, has changed it's value in the column "ispayroll" from "n" to "y" over the period of 20 days. If that's the case the new column must be set to "variable". If a combination starts out for the first time with "ispayroll" "Y" the new column value must be set to "Payroll set". If the "ispayroll" column has a "n" value the new column value must be set to "no payroll". So the result from the example above should be filled in like this:

So the condition must be checked based on the combination of Invoice Nr, Customer Key, Worker Key and Date.

Can anyone help me? I know this might be a little difficult to understand, but any help is welcome! 😉

Re: How to check if one value of a combination of values has changed over time

Greg_Deckler — Mon, 13 Jun 2022 13:08:41 GMT

Anonymous Try this:

Column = VAR __Invoice = [Invoice number] VAR __Worker = [worker key] VAR __Customer = [customer key] VAR __Table = FILTER('Table',[Invoice number] = __Invoice && [worker key] = __Worker && [customer key] = __Customer) VAR __Count = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"__Payroll",[payroll?]))) VAR __Payroll = MAXX(DISTINCT(SELECTCOLUMNS(__Table,"__Payroll",[payroll?])),[__Payroll]) RETURN SWITCH(TRUE(), __Count > 1, "Variable", __Payroll = "n", "No payroll", "Payroll set" )

Re: How to check if one value of a combination of values has changed over time

Anonymous — Mon, 13 Jun 2022 16:27:09 GMT

Hey Greg!

Thanks so much for the quick response, I'll try the code out tomorrow! 😊

Re: How to check if one value of a combination of values has changed over time

v-zhangti — Thu, 16 Jun 2022 08:31:45 GMT

Hi, Anonymous

You can try the following methods.

Result = IF ( [payroll?] = "y" && [date] = CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) ) ), "payroll set", IF ( [payroll?] = "n" && [date] = CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) ) ), "no payroll", IF ( [payroll?] = "y" && [date] > CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) ) ) + 20, "variable", IF ( [payroll?] = "n", "no payroll" ) ) ) )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

Re: How to check if one value of a combination of values has changed over time

Anonymous — Thu, 16 Jun 2022 10:04:29 GMT

Sorry for the delay, I've been sick and haven't been able to work.

This doesn't work for my questions because it doesn't take in account the 20 day rule that is needed for the "Variable" payroll option.

But it is a nice start because it does a lot of things right! Maybe there's something we can do to add the 20 day rule to your code?

Re: How to check if one value of a combination of values has changed over time

Anonymous — Thu, 16 Jun 2022 10:09:26 GMT

Hi!

Thanks so much for the code, it works and is almost perfect! But unfortunately not entirely as it needs to when I add more data to the dummy data:

As you can see in the example above, there's a blank value? This should have also been filled in with "Variable" as the combination (3, 40, 77) passed 20 days after it's first entry? We're getting close though, this is very promising and almost exactly what I need! 😊

Re: How to check if one value of a combination of values has changed over time

Anonymous — Thu, 16 Jun 2022 11:38:13 GMT

Also, the check has to be based off the combination of invoice number, worker key and customer key. If I add a new line with invoice number 1 but with a different worker key and customer key, it fills it in with "Variable" as well, when in fact it should fill it in with 'Payroll Set':

So sorry, I know what I'm asking is very complicated!

Re: How to check if one value of a combination of values has changed over time

v-zhangti — Tue, 21 Jun 2022 05:54:23 GMT

Hi, Anonymous

You can try the following methods.

Result = IF ( [payroll?] = "y" && [date] = CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) && [Customer key] = EARLIER ( 'Table'[Customer key] ) && [worker key] = EARLIER ( 'Table'[worker key] ) ) ), "payroll set", IF ( [payroll?] = "n" && [date] = CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) && [Customer key] = EARLIER ( 'Table'[Customer key] ) && [worker key] = EARLIER ( 'Table'[worker key] ) ) ), "no payroll", IF ( [payroll?] = "y" && [date] >= CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) && [Customer key] = EARLIER ( 'Table'[Customer key] ) && [worker key] = EARLIER ( 'Table'[worker key] ) ) ) + 20, "variable", IF ( [payroll?] = "n", "no payroll" ) ) ) )

This time the formula is written based on the invoice number, the worker key and the customer key.

(3, 40, 77) obtained the correct value.

As shown in the graph, (1,20,55) has been rejected for the first time after 20 days on March 1, shouldn't it be "variable"? If there are other conditions, please let me know.

Best Regards,

Community Support Team _Charlotte

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

Re: How to check if one value of a combination of values has changed over time

Anonymous — Tue, 21 Jun 2022 08:06:34 GMT

Hi!

It works! This is exactly what I needed, thanks! 😊

And you're right, it should be "Variable" for that example, I highlighted the wrong row in my screenshot sorry. I meant to highlight the row above, which had the same invoice number, but different customer key and worker key. But that issue has been resolved with the new code you sent me so no worries! 👍

I'm gonna try to implement this in our actual dataset and hopefully everything will work exactly as it does with the dummy data we used here!

Re: How to check if one value of a combination of values has changed over time

Anonymous — Fri, 05 Apr 2024 09:34:36 GMT

Hey Greg

Is there a way to add the 20 days rule into account in this code? 🙂