topic Re: Build a SPC warning in DAX in DAX Commands and Tips

Build a SPC warning in DAX

Magnusnilsson — Fri, 22 Dec 2023 07:41:52 GMT

Hi,

I cant find that this topic has been brought up before.

I am trying to find a way to make a DAX command that gives me a mark when a couple of factors are happening.

I am following a process in a industri.

The process is giving me a value that I compare to a target and show this as a % difference by date it is recorded.

Each value is connected to a customer.

Can I get code in DAX that give me a let say a "1" when value having 5 times in a row over target...? by customer.

Table

Thanks in advance

Re: Build a SPC warning in DAX

lbendlin — Sun, 24 Dec 2023 21:29:14 GMT

yes, that is possible.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Build a SPC warning in DAX

Anonymous — Tue, 26 Dec 2023 02:34:47 GMT

Hi Magnusnilsson

I have understood your approximate needs.

You want to mark values that are consecutively outside a set range with a 1.

Here is the solution:

First of all, I've created a set of data to use as an example:

Then click New Column and enter the DAX expression like this:

------------------------------------------------------------------------------------

warn =

var a = COUNTROWS(

FILTER('Table',[Customer]=EARLIER('Table'[Customer])&&[%]<=EARLIER('Table'[%]))

)

var b = IF(a>=5,1)

return b

------------------------------------------------------------------------------------

Variable a is used to compare sales with context and then sort it.

The results after running are as follows:

Best Regards,

Zhengdong Xu

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

Re: Build a SPC warning in DAX

Magnusnilsson — Tue, 02 Jan 2024 09:53:15 GMT

Hi,

First solution didnt work like I wanted.

I attach a table that contains the data set.

Little more information from me.

I would like the warning to be on a upper and a lower stage...

For instance if the value is lower than -0,15 and over 0,15 and more than 5 in a row this should trigger the warning.

Date	Customer	Order	%	Ex. Warning
2020-02-20	1104851-10	1598519-10	0,1
2020-02-20	1104851-10	1598520-10	0,1
2020-02-20	1104851-10	1598522-10	0,14
2020-02-28	1104851-10	1598523-10	-0,04
2020-03-05	1136115-10	1590141-100	0,08
2020-03-05	1136115-10	1590141-90	0,08
2020-03-11	1128933-10	1602377-10	0,03
2020-03-12	1128933-10	1602377-1	-0,08
2020-04-09	1128933-10	1602379-10	-0,04
2020-04-13	1136115-10	1590141-11	-0,07
2020-04-13	1136115-10	1590141-160	-0,07
2020-04-14	1128933-10	1602379-1	-0,07
2020-04-24	1104851-10	1602002-10	0,06
2020-04-24	1104851-10	1602007-10	0,04
2020-05-22	1128933-10	1602380-1	-0,04
2020-05-25	1128933-10	1602380-10	-0,03
2020-06-14	1136115-10	1590141-170	-0,09
2020-06-14	1136115-10	1590141-200	-0,02
2020-07-06	1128933-10	1615277-10	0,17
2020-07-08	1104851-10	1602014-10	0,02
2020-07-09	1104851-10	1602008-10	0,16
2020-07-10	1104851-10	1602012-10	0,16
2020-08-07	1128933-10	1615279-10	0,16
2020-08-07	1128933-10	1615280-10	0,16
2020-08-20	1128933-10	1615283-10	0,18
2020-08-21	1128933-10	1615281-10	0,18	1
2020-08-21	1128933-10	1615285-10	0,4
2020-08-24	1136115-10	1590141-21	-0,16
2020-08-24	1136115-10	1590141-240	-0,2
2020-08-26	1136115-10	1590141-220	-0,19
2020-10-15	1104851-10	1602023-10	0,19
2020-10-16	1104851-10	1602017-10	0,19
2020-11-03	1104851-10	1602019-10	0,33	1
2020-11-22	1136115-10	1627332-10	-0,17
2020-11-22	1136115-10	1627332-40	-0,2	1

Re: Build a SPC warning in DAX

lbendlin — Tue, 02 Jan 2024 14:57:41 GMT

What is the target?

Can you guarantee that there will be maximum only one row per customer per day?

Re: Build a SPC warning in DAX

Magnusnilsson — Wed, 03 Jan 2024 07:09:39 GMT

Hi,

Target is zero (0).

And no there can be more than one row per customer per day...

But if it helps we can use the average of each day per customer to get one row per customer.

/Magnus

Re: Build a SPC warning in DAX

Anonymous — Thu, 04 Jan 2024 05:59:34 GMT

Hi Magnusnilsson

I'm sorry for the mistake in my previous answer, I redid it according to your needs:

I created a table using the data you provided:

Then a calculated column is created:

warn = var Greater_ = COUNTROWS(FILTER('Table',[Date]<=EARLIER('Table'[Date])&&[Customer]=EARLIER('Table'[Customer])&&[%]>15)) var Lower_ =COUNTROWS(FILTER('Table',[Date]<=EARLIER('Table'[Date])&&[Customer]=EARLIER('Table'[Customer])&&[%]<-15)) RETURN IF([%]>15&&Greater_>=5,1,IF([%]<-15&&Lower_>=5,1))

Greater_ and Lower_ are counted for the same user who has exceeded 15 consecutive years and is below 15 before this date, respectively
The result is as follow:

The reason for the discrepancy between the final result and your result may be that if my calculation column is out of range multiple times on the same day, it will only be counted once on that day.

^{Best Regards,}

^{Zhengdong Xu}

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