cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
req7
Helper IV
Helper IV

Count when the same value is repeated 3 times in a row in the table

Hello 🙂
My table follows this structure:

Product_Code   Classification   Machine   Time
AAA   1   MACHINE_01   26/04/2021 05:18:00
AAA   3   MACHINE_01   26/04/2021 08:19:20
AAA   3   MACHINE_01   26/04/2021 10:28:29
BBB   3   MACHINE_02   26/04/2021 18:00:00
BBB   3   MACHINE_02   26/04/2021 18:00:00
CCC   1   MACHINE_01   26/04/2021 07:00:00
CCC   1   MACHINE_01   26/04/2021 07:05:00
CCC   3   MACHINE_01   26/04/2021 07:11:00
CCC   1   MACHINE_01   26/04/2021 07:19:00
DDD   2   MACHINE_03   26/04/2021 10:07:09
DDD   1   MACHINE_03   26/04/2021 10:58:10
EEE   3   MACHINE_02   26/04/2021 11:10:10
EEE   3   MACHINE_02   26/04/2021 11:29:00
EEE   3   MACHINE_02   26/04/2021 11:37:15

 

  • Column Product_Code shows the code of each product (each product has a unique code, but it can appear more than once in the table because the same product can have different classifications over time).
  • Column Classification shows the classification of this product (whether it is good (1), acceptable (2) or bad (3)).
  • Column Machine shows which machine this product was created on.
  • Column Time shows the date and time the product was created.

 

I would like to create a measure that serves as a counter for the following occurrence:

If the same classification occurs for the same product and Machine 3 times in a row (based on the Time column) the counter measure must add +1.

So, for my example table above, the counter measure should bring me a value 1, because only the EEE product had 3 equal classifications in a row and for the same product and the same machine.

Can someone help me?

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @req7 ,

 

Sorry for the late reply!

First create an index column;

Then create 2 columns as below:

Check = 
var _classification= 'Table'[   Classification]
var _code='Table'[Product_Code]
var  _machine='Table'[   Machine]
var _count=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[   Classification]=_classification&&'Table'[   Machine]=_machine&&'Table'[Product_Code]=_code))
Return
_count
count = 
var _nextvalue= CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])+1))
var _previousvalue=CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Check]=3&&_nextvalue=3&&_previousvalue=3,1,BLANK())

And you will see:

v-kelly-msft_0-1620630440967.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

Hi @req7 ,

 

Why CCC is not included in the result?

CCC    1    MACHINE_01    26/04/2021 07:00:00
CCC    1    MACHINE_01    26/04/2021 07:05:00
CCC    3    MACHINE_01    26/04/2021 07:11:00
CCC    1    MACHINE_01

   26/04/2021 07:19:00

 

 

Best Regards,
Kelly

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

 

Hi, @v-kelly-msft 
Because I would like the measure to count 3 equal classifications in a row.

CCC has two classifications = 1, then one classification = 3 and then one classification = 1.

So CCC had 3 classifications = 1, but it wasn't 3 in a row

v-kelly-msft
Community Support
Community Support

Hi  @req7 ,

 

Sorry for the late reply!

First create an index column;

Then create 2 columns as below:

Check = 
var _classification= 'Table'[   Classification]
var _code='Table'[Product_Code]
var  _machine='Table'[   Machine]
var _count=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[   Classification]=_classification&&'Table'[   Machine]=_machine&&'Table'[Product_Code]=_code))
Return
_count
count = 
var _nextvalue= CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])+1))
var _previousvalue=CALCULATE(MAX('Table'[Check]),FILTER(ALL('Table'),'Table'[Product_Code]=EARLIER('Table'[Product_Code])&&'Table'[Index]=EARLIER('Table'[Index])-1))
Return
IF('Table'[Check]=3&&_nextvalue=3&&_previousvalue=3,1,BLANK())

And you will see:

v-kelly-msft_0-1620630440967.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

Amazing, @v-kelly-msft! This works fine.
Thanks!!! 🙂

daxer
Solution Sage
Solution Sage

@req7 

 

Here's a measure that does it. But be careful as it respects all the filters put on the table. That means, for instance, that after filtering you can obtain a classification run that will qualify as per your requirements but if you remove the filter, the same set of previously filtered rows will NOT qualify anymore. Please play with the measure to see how it behaves.

 

If you don't slice by Time and/or by Classification, the result will be as you'd expect. But if you start slicing by the latter, you have to understand that then you are potentially filtering out rows that might disturb the time sequence of classifications and hence produce a run that will qualify as per the requirements. Just play with the measure and you'll see it works correctly BUT HONORS all filters on the table.

 

 

Counts = 
var vMinimumRunLength = 3
var vResult =
    SUMX(
        SUMMARIZE(
            T,
            T[Product_Code],
            T[Machine]
        ),
        // Need to check if there is a classification
        // that occurs at least 3 times in a row for
        // the combination above and if it does, then
        // the expression below should return 1.
        var vClassificationWithTime =
            CALCULATETABLE(
                SUMMARIZE(
                    T,
                    T[Classification],
                    T[Time]
                )
            )
        var vClassificationWithTimeOrder =
            ADDCOLUMNS(
                vClassificationWithTime,
                "@TimeOrder",
                    var vCurrentTime = T[Time]
                    return
                    RANKX(
                        vClassificationWithTime,
                        T[Time],
                        vCurrentTime,
                        ASC
                    )
            )
        var vClassificationWithChangeIndicator =
            ADDCOLUMNS(
                vClassificationWithTimeOrder,
                "@ChangeIndicator",
                    var vCurrentClassification = T[Classification]
                    var vCurrentTimeOrder = [@TimeOrder]
                    var vPreviousClassification =
                        MAXX(
                            FILTER(
                                vClassificationWithTimeOrder,
                                [@TimeOrder] = vCurrentTimeOrder - 1
                            ),
                            T[Classification]
                        )
                    var vIsChange =
                        vPreviousClassification <> vCurrentClassification
                    return
                        int( vIsChange )
            )
        var vClassificationWithGroupId =
            ADDCOLUMNS(
                vClassificationWithChangeIndicator,
                "@GroupId",
                    var vCurrentTimeOrder = [@TimeOrder]
                    var GroupId =
                        SUMX(
                            filter(
                                vClassificationWithChangeIndicator,
                                [@TimeOrder] <= vCurrentTimeOrder
                            ),
                            [@ChangeIndicator]
                        )
                    return
                        GroupId
            )
        var vSameClassificationLongestRun =
            MAXX(
                GROUPBY(
                    vClassificationWithGroupId,
                    [@GroupId],
                    "@RunLength",
                        SUMX( CURRENTGROUP(), 1 )
                ),
                [@RunLength]
            )
        var vIsLongestRunEnough =
            vSameClassificationLongestRun >= vMinimumRunLength
        return
            INT( vIsLongestRunEnough )
    )
return
    vResult

 

 

Hi, @daxer 

Thank you very much for your answer 🙂 it took me a while to answer because I was testing your measurement, as you said. I realized that, as you informed, the filter in the tables can change the count number of the measure, which I think can be a problem.

Why? I wanted to create this counting measure just to create an email alert in Power BI based on that measure. My Power BI updates once a day, so I wanted that when I updated, this measure would count the occurrences of the previous day, and if the value was greater than 0, then the email would be sent to people, informing them that there were products with 3 same classifications in a row for the same machine.

I'm still trying to use your measure, but sometimes, when I filter only on a specific day, it shows a different occurrence count than it should show.

I don't know what else I can do 😞

daxer
Solution Sage
Solution Sage

@req7 

 

Hi there. If you want to stop the measure being sensitive to some filters, then create a new one based on the above that will be to your liking. You could do something like:

 

[Counts Adjusted] =
calculate(
    [Counts],
    // removing filters, if any, from
    // Column1, Column2,... of Table1
    all( Table1[Column1], Table1[Column2],... ),
    // same for Table2... and so on...
    all( Table2[Column1], Table2[Column2],... ),
    ...
)

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors