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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VinicioAraujo
New Member

Cumulative Total by Group and With Reset

Hello!

I have a report in Power BI to analyze if a lot of equipments went to production line or to cleaning system (Destiny). It is a huge database (100k lines for 1 month of analyses).

 

The objetive is check how many times the equipment(Equipment) went to production line without cleaning, always following time(Timestamp) order. After being cleaned, the counter goes to zero. It must be sliced in future report charts.

 

Here is my data format:

 

DestinyEquipmentTimestamp
Production LineS10304/03/2022 14:12:45
Production LineS11004/03/2022 14:17:34
CleaningS11004/03/2022 15:12:23
Production LineS10304/03/2022 18:34:11
CleaningS10305/03/2022 20:34:43
Production LineS11005/03/2022 13:11:55
Production LineS10305/03/2022 18:19:49
Production LineS11005/03/2022 23:34:22
CleaningS11006/03/2022 01:02:04
Production LineS09707/03/2022 04:08:45
Production LineS09707/03/2022 05:01:05
Production LineS09707/03/2022 09:37:25
Production LineS09707/03/2022 14:48:15
CleaningS09707/03/2022 23:34:12

 

The expected analyse is:

*The equipment S103 went to production line 2 times between 04/03/2022 14:12:45 and 05/03/2022 20:34:43 until get cleaned;

*The equipment S110 went to production line 1 time until get cleaned at 04/03/2022 15:12:23;

*The equipment S110 went to production line 2 times between 05/03/2022 13:11:55 and 06/03/2022 01:02:04 until get cleaned;

*The equipment S103 went to production line 1 time in 05/03/2022 18:19:49.

*The equipment S097 went to production line 4 times between 07/03/2022 04:08:45 and 07/03/2022 23:34:12 until get cleaned.

 

The expected table (with addition columns) is:

 

DestinyEquipmentTimestamp

Running Total (w/o cleaning)

Production LineS10304/03/2022 14:12:451
Production LineS11004/03/2022 14:17:341
CleaningS11004/03/2022 15:12:230
Production LineS10304/03/2022 18:34:112
CleaningS10305/03/2022 20:34:430
Production LineS11005/03/2022 13:11:551
Production LineS10305/03/2022 18:19:491
Production LineS11005/03/2022 23:34:222
CleaningS11006/03/2022 01:02:040
Production LineS09707/03/2022 04:08:451
Production LineS09707/03/2022 05:01:052
Production LineS09707/03/2022 09:37:253
Production LineS09707/03/2022 14:48:154
CleaningS09707/03/2022 23:34:120

 

Could someone help with this?

 

Remember that my datasize is big (100k rows/month), maybe dax is not a good solution.

 

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

Hi @VinicioAraujo ,

According to your description, here's my solution.

Create a calculated column.

Column =
VAR _MIN =
    MINX (
        FILTER (
            'Table',
            'Table'[Equipment] = EARLIER ( 'Table'[Equipment] )
                && 'Table'[Destiny] = "Cleaning"
                && 'Table'[Timestamp] > EARLIER ( 'Table'[Timestamp] )
        ),
        'Table'[Timestamp]
    )
VAR _MAX =
    MAXX (
        FILTER (
            'Table',
            'Table'[Equipment] = EARLIER ( 'Table'[Equipment] )
                && 'Table'[Destiny] = "Cleaning"
                && 'Table'[Timestamp] < EARLIER ( 'Table'[Timestamp] )
        ),
        'Table'[Timestamp]
    )
RETURN
    IF (
        'Table'[Destiny] = "Cleaning",
        0,
        RANKX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Equipment] = EARLIER ( 'Table'[Equipment] )
                    && 'Table'[Timestamp]
                        > IF ( _MAX = BLANK (), DATE ( 2012, 1, 1 ), _MAX )
                    && 'Table'[Timestamp]
                        < IF ( _MIN = BLANK (), TODAY (), _MIN )
            ),
            'Table'[Timestamp],
            ,
            ASC,
            DENSE
        )
    )

Date 2012/1/1 in my formula is a small enough number is chosen that is smaller than any date in the sample.

Get the expected result.

vkalyjmsft_0-1648030891880.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

If this posthelps, then please considerAccept it as the solutionto help the other members find it more quickly.   

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @VinicioAraujo ,

According to your description, here's my solution.

Create a calculated column.

Column =
VAR _MIN =
    MINX (
        FILTER (
            'Table',
            'Table'[Equipment] = EARLIER ( 'Table'[Equipment] )
                && 'Table'[Destiny] = "Cleaning"
                && 'Table'[Timestamp] > EARLIER ( 'Table'[Timestamp] )
        ),
        'Table'[Timestamp]
    )
VAR _MAX =
    MAXX (
        FILTER (
            'Table',
            'Table'[Equipment] = EARLIER ( 'Table'[Equipment] )
                && 'Table'[Destiny] = "Cleaning"
                && 'Table'[Timestamp] < EARLIER ( 'Table'[Timestamp] )
        ),
        'Table'[Timestamp]
    )
RETURN
    IF (
        'Table'[Destiny] = "Cleaning",
        0,
        RANKX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Equipment] = EARLIER ( 'Table'[Equipment] )
                    && 'Table'[Timestamp]
                        > IF ( _MAX = BLANK (), DATE ( 2012, 1, 1 ), _MAX )
                    && 'Table'[Timestamp]
                        < IF ( _MIN = BLANK (), TODAY (), _MIN )
            ),
            'Table'[Timestamp],
            ,
            ASC,
            DENSE
        )
    )

Date 2012/1/1 in my formula is a small enough number is chosen that is smaller than any date in the sample.

Get the expected result.

vkalyjmsft_0-1648030891880.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

If this posthelps, then please considerAccept it as the solutionto help the other members find it more quickly.   

Hello.

 

@v-yanjiang-msft your solutions works perfectly.

 

Thanks for help! If you don't mind about, could you please explain your code?

maybe with //comments tags.

 

It is advanced level of dax to me.

lbendlin
Super User
Super User

"It must be sliced in future report charts"

 

Technically your data is immutable, so this should be done in Power Query or as a calculated column.  But your comment is a red flag.  Are you planning to arbitrarily limit the timeframe that is being looked at?  In that case it would need to be implemented as a measure.  Please clarify.

 

Here is a proposal for a Power Query implementation.  No idea how it performs though.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZIxC8IwEIX/Sslc6N3lYppbXR0Ex9JBtEhAUhD9/yYWWjCN1CnL+969e5euU8fHeH1dnn4M1cGHQdXqhKDjA9yAbgiIKmRBEjaqr9cBhBywovkD7O/DOfhwW1eaZE26aJ1laaOvIObWk9LMSoKk5LL1lGUBUEdfMeU1vyfELOiE3eYJpFMkolIvu1kJKEACXLIGZxNgF4AF2h83ygEjacgfgBNthbYD8RdwbMhk22bKqReMvfRv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Destiny = _t, Equipment = _t, Timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Destiny", type text}, {"Equipment", type text}, {"Timestamp", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Equipment"}, {{"Grouped", each _, type table [Destiny=nullable text, Equipment=nullable text, Timestamp=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Converted", each let #"Added Index1" = Table.AddIndexColumn(Table.SelectRows(Table.AddIndexColumn([Grouped], "Index", 0, 1, Int64.Type), each ([Destiny] = "Cleaning")), "Index.1", 0, 1, Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(Table.AddColumn(#"Added Index1", "Count", each if [Index.1] = 0 then [Index] else [Index] - #"Added Index1"{[Index.1]-1}[Index] - 1),{"Timestamp", "Count"})
in
    #"Removed Other Columns"),
    #"Expanded Converted" = Table.ExpandTableColumn(#"Added Custom", "Converted", {"Timestamp", "Count"}, {"Timestamp", "Count"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Converted",{"Equipment", "Timestamp", "Count"})
in
    #"Removed Other Columns"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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