Build a SPC warning in DAX

[Magnusnilsson]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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.

[lbendlin]

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

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/1447...

[Magnusnilsson]

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

[lbendlin]

What is the target?

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

[Magnusnilsson]

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