cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Route217
Responsive Resident
Responsive Resident

6 or more data Points Close together on Detection Line

Hi Experts

 

See link to file. It is possible in a line chart to hightlight 6 or more data points either moving up or down close together. Kindly look at the PBIOX file and the Detection line in order to bring out / highlight the trend.

 

See Pdf document Section C...

https://www.dropbox.com/s/m1bc3i17zuhaphp/Intro%20to%20Control%20Charts%20for%20Project%20DROID.pdf?...

 

Sample File

https://www.dropbox.com/s/tc5jw2mlqrhd1j8/SPC%20Sample%20Datat.pbix?dl=0 

1 ACCEPTED SOLUTION

@Route217 - OK, I have this fully implemented as columns for Lower Trend, Upper Trend and No Trend. PBIX is attached. Next step is to make these measure. Hang tight. Had to modify the trend code to make an extra pass to eliminate false positives. The Lower Trend code is now the following. The Upper Trend and No Trend are similar and in the PBIX.

 

Lower Trend = 
    VAR __Trend = 6
    VAR __Current = [Index]
    VAR __Average = [Average Monthly Value]
    VAR __Table = 
        FILTER(
            ADDCOLUMNS(
                FILTER(ALL('SPC Chart Data'),[Index] >= __Current - __Trend + 1 && [Index] <= __Current + __Trend - 1),
                "__Diff",[Detections] - [Average Monthly Value]
            ),
            [__Diff] < 0
        )
    VAR __Table1 = 
        FILTER(
            ADDCOLUMNS(
                __Table,
                "__IndexDiff",[Index] - MAXX(FILTER(__Table,[Index]<EARLIER([Index])),[Index])
            ),
            [__IndexDiff] = 1 || [Index] = [__IndexDiff]
        )
    VAR __Table2 = 
        FILTER(
            ADDCOLUMNS(
                __Table1,
                "__IndexDiff2",[Index] - MAXX(FILTER(__Table1,[Index]<EARLIER([Index])),[Index])
            ),
            [__IndexDiff2] = 1 || [Index] = [__IndexDiff2]
        )
RETURN
    IF(COUNTROWS(__Table2) >= __Trend && [Detections] < [Average Monthly Value],[Detections],BLANK())

 


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
Greg_Deckler
Super User IV
Super User IV

@Route217 - You can ditch the Upper Bound and Lower Bound in your line graph and instead use the Analysis Pane to add a Max line and a Min line to make it dynamic. As for identifying trends. My thinking here is to create  three measures, one for upper trends, one for lower trends and one for no trend. In short, each measure would look at the surrounding data points and identify if there was a trend upwards, downwards or no trend and would only return values for the specific type of trend/non-trend and return blank otherwise. Then you would use all three measures in your line graph. Then you would have different colors assigned to each and thus achieve your goal. That's the theory, I'll see if I can put it into practice with your data.


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Greg, Many thanks once again, sound super complex.

@Route217 - I implemented this concept as a column using the following DAX. Definitely no reason it could not be implemented as a measure and I'll work on that. In the event that I get this all buttoned up, would it be OK if I used your PBIX file in a submission to the Quick Measures Gallery? If not, I'll invent some data in a separate PBIX file. Will definitely reference you and this thread. The following DAX only returns results when it has identified a trend of the length specified by the __Trend variable and this is for trends lower than the average. Very simple to change this to greater than the average and with minor modifications could even return values for any trend (both lower than and greater than). The "non-trend" version would simply flip the IF statement in the RETURN.

Lower Trend = 
    VAR __Trend = 6
    VAR __Current = [Index]
    VAR __Average = [Average Monthly Value]
    VAR __Table = 
        FILTER(
            ADDCOLUMNS(
                FILTER(ALL('SPC Chart Data'),[Index] >= __Current - 5 && [Index] <= __Current + 5),
                "__Diff",[Detections] - [Average Monthly Value]
            ),
            [__Diff] < 0
        )
    VAR __Table1 = 
        FILTER(
            ADDCOLUMNS(
                __Table,
                "__IndexDiff",[Index] - MAXX(FILTER(__Table,[Index]<EARLIER([Index])),[Index])
            ),
            [__IndexDiff] = 1
        )
RETURN
    IF(COUNTROWS(__Table1) + 1 >= __Trend && [Detections] < [Average Monthly Value],[Detections],BLANK())

 


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Greg Silly Question there is no such thing as a Upper trend??? we could add...

Hi Greg - happy for you to use my file the data is made up noting confidential. Could you kinldy post a PBIX file with the about Measure as a column?

@Route217 - OK, I have this fully implemented as columns for Lower Trend, Upper Trend and No Trend. PBIX is attached. Next step is to make these measure. Hang tight. Had to modify the trend code to make an extra pass to eliminate false positives. The Lower Trend code is now the following. The Upper Trend and No Trend are similar and in the PBIX.

 

Lower Trend = 
    VAR __Trend = 6
    VAR __Current = [Index]
    VAR __Average = [Average Monthly Value]
    VAR __Table = 
        FILTER(
            ADDCOLUMNS(
                FILTER(ALL('SPC Chart Data'),[Index] >= __Current - __Trend + 1 && [Index] <= __Current + __Trend - 1),
                "__Diff",[Detections] - [Average Monthly Value]
            ),
            [__Diff] < 0
        )
    VAR __Table1 = 
        FILTER(
            ADDCOLUMNS(
                __Table,
                "__IndexDiff",[Index] - MAXX(FILTER(__Table,[Index]<EARLIER([Index])),[Index])
            ),
            [__IndexDiff] = 1 || [Index] = [__IndexDiff]
        )
    VAR __Table2 = 
        FILTER(
            ADDCOLUMNS(
                __Table1,
                "__IndexDiff2",[Index] - MAXX(FILTER(__Table1,[Index]<EARLIER([Index])),[Index])
            ),
            [__IndexDiff2] = 1 || [Index] = [__IndexDiff2]
        )
RETURN
    IF(COUNTROWS(__Table2) >= __Trend && [Detections] < [Average Monthly Value],[Detections],BLANK())

 


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thank you sir

Also can you remove this file too. As it has branding - again my fault apologies...

@Route217 - Sure, deleted the PBIX file posted. Wiped the branding. New file is attached with the measures implementation. One thing I am going to work on is seeing if I can "connect" the lines. I have a thought on something that might work. You want the "Duplicate of SPC Chart" page. The other last page was me seeing if I could get the connected line to work with default Power BI settings.


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

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Greg, Excellent and many thanks - Can you remove any logo's from the file and the Home Page and branding before using on your personal page. My Fault.

lbendlin
Super User III
Super User III

That's not something you can do in standard Power BI.  Either use R/Python script visuals or create your own custom visual that implements the pattern algorithm.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.