Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Anonymous
Not applicable

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?

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you sir

Anonymous
Not applicable

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

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.