skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Re: Cthulhu

    Re: Cthulhu

    04-09-2019 13:19 PM

    eugenm
    Helper I
    24545 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Cthulhu

    ‎09-06-2018 03:27 PM

    Why Cthulhu? Because the mental gymnastics required to figure this out nearly drove me insane. And because the exact reason you would need this measure is probably beyond the comprehension of mere mortals. Besides, what else am I supposed to call it, "Repeating Counter Indexing Thingy"?

     

    But, if you need a column or measure that counts a group of things consequetively but restarts after a non-consecutive row, well then you are likely the second person to need this...

     

     

    Cthulhu = 
    VAR __index = CALCULATE(MAX([Index])) //What is my current row index?
    VAR __group = CALCULATE(MAX([Animal])) //What is my current group?
    VAR __tmpTable1 = FILTER(ALL('Cthulhu'),[Animal]=__group&&[Index]<__index) //Return all rows earlier than the current row within the same "group"
    VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Cthulhu'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) //For each returned row, calculate the difference between the current index value and the previous index value within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1
    VAR __max = MAXX(__tmpTable2,[Index]) //Figure out the max index in the current filtered table.
    VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again) This will be the greatest index where the difference from the previous index in the same group is greater than 1 (previous row)
    VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip
    RETURN IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip.

    The other person would be this guy @Anonymous in this thread: https://community.powerbi.com/t5/Desktop/Consecutive-Row-Counter-Column/td-p/509553/highlight/false

     

     

     

     

     

     

    eyJrIjoiMTBhYmFlZjMtZDhhMy00MGFjLThkZWQtNDc5MDM2M2ZjN2UzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ 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...
    Preview file
    129 KB
    cthulhu.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 7
    26,307 Views
    9
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    eugenm
    eugenm Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-08-2019 03:09 PM

    I may be a 3rd guy... Looks like I may be using this work.

    I would have preferred a Power Query based solution, as the mix of PQ and using add column or measure is a bit too unpredictable for a beginner like me. I have lots of PQ in a complex table, along with using this, as an add column.

    It worked! But devs are buffled as to how it is all works. 

     

    Amazing work - I've just signed up to say thank you!... 

    Message 4 of 7
    24,595 Views
    2
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to eugenm
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-09-2019 06:21 AM

    @eugenm - Glad it is of use! It was a fun one to put together. For a Power Query version, your best bet would be @ImkeF !


    @ 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...
    Message 5 of 7
    24,555 Views
    0
    Reply
    ImkeF
    Super User ImkeF
    Super User
    In response to Greg_Deckler
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-09-2019 01:15 PM

    I call that "Nested Index"-column: https://www.youtube.com/watch?v=-3KFZaYImEY

     

    Table.Group(YourTable, {"Animal"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1), type table}})

     

    and expand 😉

    Imke Feldmann (The BIccountant)

    If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

    How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

    Message 6 of 7
    24,547 Views
    3
    Reply
    eugenm
    eugenm Helper I
    Helper I
    In response to ImkeF
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-09-2019 01:19 PM

    Thank you. I will circle back, should I have any questions.

    Your help is much appreciated.

    Message 7 of 7
    24,545 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-07-2018 09:41 AM

    Hahaha, I wish the people using this visual truly know the blood sweat and tears behind it! Thanks for your hard work Greg!

    Message 2 of 7
    26,259 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-07-2018 09:44 AM

    Note: this is used to splice up a sensor input by shift hour: ie

     

    shift 1:

    7am hour 1

    8am hour 2

    9am hour 3

     

    Shift 2:

    10pm hour 1

    11pm hour 2

    .....

     

    Not for counting zoo animals!

    Message 3 of 7
    26,260 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices