cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Merging multiple rows into one with conditions and Filtering issue

Hi there, 

First time posting here so, bear with me. I tried to find answers to my problems but I couldn't find the one I could apply to my situation. 

I am trying to calculate number of alarms, downtime and other measures from the data source that contains daily csv files. Some machines' alarms last more than a day but they are captured as separate entry in the next csv logs. Small part of my data looks like this:

Machine IDAlarm TypeSTART_DTEND_DTDowntime
456F2/03/2020 0:002/03/2020 3:563.95
456W2/03/2020 0:002/03/2020 3:563.95
774F2/03/2020 5:272/03/2020 6:301.06
774W2/03/2020 5:272/03/2020 6:301.06
1339B29/02/2020 0:001/03/2020 0:0024.00
1339B1/03/2020 0:002/03/2020 0:0024.00
1339B2/03/2020 0:003/03/2020 0:0024.00
1339B3/03/2020 0:004/03/2020 0:0024.00
1339B4/03/2020 0:005/03/2020 0:0024.00
3288F29/02/2020 7:4529/02/2020 12:314.77
3479B29/02/2020 0:0029/02/2020 13:4013.67
4735F29/02/2020 8:0029/02/2020 18:4410.72
5412B29/02/2020 0:001/03/2020 0:0024.00
5412B1/03/2020 0:002/03/2020 0:0024.00
5412B2/03/2020 0:003/03/2020 0:0024.00
5412B8/03/2020 0:009/03/2020 0:0024.00
5412B9/03/2020 0:0010/03/2020 0:0024.00
5412B10/03/2020 0:0010/03/2020 10:5510.91
5412F10/03/2020 10:5510/03/2020 11:030.14
5851B29/02/2020 15:2529/02/2020 19:514.44
6405B29/02/2020 0:0029/02/2020 7:007.00

 

I'd like to merge a machine with the same alarm type into one row. I have years worth of logs with 35k rows in each log. So, I am not sure in which way I should merge the rows (M code or DAX measure). The result should be like this:

 

Machine IDAlarm TypeSTART_DTEND_DTDowntime
6405B29/02/2020 0:0029/02/2020 7:007.00
1339B29/02/2020 0:005/03/2020 0:00120
3479B29/02/2020 0:0029/02/2020 13:4013.67
5412B29/02/2020 0:003/03/2020 0:0072.00
3288F29/02/2020 7:4529/02/2020 12:314.77
4735F29/02/2020 8:0029/02/2020 18:4410.72
5851B29/02/2020 15:2529/02/2020 19:514.44
456F2/03/2020 0:002/03/2020 3:563.95
456W2/03/2020 0:002/03/2020 3:563.95
774F2/03/2020 5:272/03/2020 6:301.06
774W2/03/2020 5:272/03/2020 6:301.06
5412B8/03/2020 0:0010/03/2020 10:5558.91
5412F10/03/2020 10:5510/03/2020 11:030.14

 

The second issue I have is filtering downtime and other measures monthly. Since some alarms last for days, their duration could span across 2 months. How can I filter it in such a way that my dashboard shows the correct measures for that particular month only. For example, machine (1339) has an alarm B for 24hrs in Feb and 96hrs in Mar. How can I capture that?

Thanks heaps in advance. 

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Merging multiple rows into one with conditions and Filtering issue

This file should help you split

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

These measures should help you after the split to get data

Min(Table[Start Date])

Max(Table[EndDate])

Sum(Table[Downtime])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Merging multiple rows into one with conditions and Filtering issue

Hi,

 

Please take following steps:

For your requirement 1:

1)Add an index column to original table first.

2)Create a new index column:

 

New Index = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[END_DT] ),
        FILTER (  'Table' , 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
    var b = CALCULATE (
        MAX ( 'Table'[START_DT] ),
        FILTER (  'Table' , 'Table'[Index] = EARLIER( 'Table'[Index] ) + 1 )
    )
VAR check =
    IF (
        ( 'Table'[START_DT]  = a||'Table'[END_DT]=b)
            ,
        CALCULATE(MIN ('Table'[Index]),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Machine ID] =EARLIER('Table'[Machine ID])
                    && 'Table'[Alarm Type] =EARLIER('Table'[Alarm Type])
            )
        
        ),
        'Table'[Index]
    )
RETURN
    check

 

3)The result shows:

30.PNG

For your requirement 2:

1)Create a Month column and a new group column:

 

Month = MONTH('Table'[START_DT])

New Group = 
IF (
    MONTH ( 'Table'[START_DT] ) <> MONTH ( 'Table'[END_DT] ),
    'Table'[Machine ID] & "-" & 'Table'[Alarm Type] & "-1",
    'Table'[Machine ID] & "-" & 'Table'[Alarm Type] & "-2"
)

 

2)The result shows:

31.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

Highlighted
Frequent Visitor

Re: Merging multiple rows into one with conditions and Filtering issue

Hi

Thanks for the reply. 

I have years worth of data so ther is not enough memory to create calculated columns. 

Is there any chance that we can create measures?

=====

And the logic should be like this?

IF

machineID = earlier machineID AND

Start date = earlier End date AND

Alarm type  = earlier Alarm type

Then, those multiple rows should combine as one. 

Because on the same day, the same alarm type could appear multiple times with differnt timestamps. 

 

 

 

Highlighted
Super User IV
Super User IV

Re: Merging multiple rows into one with conditions and Filtering issue

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Merging multiple rows into one with conditions and Filtering issue

Hi, 

Thank you for the reply, however, there is still an issue.

Not only do I need the total downtime, I also need to calculate alarm quantity as well. 

What if machine 1339 and 5412 have alarms B like this:

 

Machine IDAlarm TypeSTART_DTEND_DTDowntime
456F2/03/2020 0:002/03/2020 3:563.95
456W2/03/2020 0:002/03/2020 3:563.95
774F2/03/2020 5:272/03/2020 6:301.06
774W2/03/2020 5:272/03/2020 6:301.06
1339B29/02/2020 0:001/03/2020 0:0024.00
1339B1/03/2020 0:002/03/2020 0:0024.00
1339B2/03/2020 0:003/03/2020 0:0024.00
1339B3/03/2020 0:004/03/2020 0:0024.00
1339B4/03/2020 0:005/03/2020 0:0024.00
1339B5/03/2020 8:005/03/2020 10:002.00
1339B5/03/2020 19:006/03/2020 2:007.00
3288F29/02/2020 7:4529/02/2020 12:314.77
3479B29/02/2020 0:0029/02/2020 13:4013.67
4735F29/02/2020 8:0029/02/2020 18:4410.72
5412B29/02/2020 0:001/03/2020 0:0024.00
5412B1/03/2020 0:002/03/2020 0:0024.00
5412B2/03/2020 0:003/03/2020 0:0024.00
5412B8/03/2020 0:009/03/2020 0:0024.00
5412B9/03/2020 0:0010/03/2020 0:0024.00
5412B10/03/2020 0:0010/03/2020 10:5510.91
5412B10/03/2020 10:5810/03/2020 11:581.00
5412F10/03/2020 10:5510/03/2020 11:030.14
5851B29/02/2020 15:2529/02/2020 19:514.44
6405B29/02/2020 0:0029/02/2020 7:007.00

 

And the result should be like below. The total alarm would be 16 in this case. Notice the alarms for machine 5412 has reduced from 8 to 4.

Machine IDAlarm TypeSTART_DTEND_DTDowntime
6405B29/02/2020 0:0029/02/2020 7:007.00
1339B29/02/2020 0:005/03/2020 0:00120
1339B5/03/2020 8:005/03/2020 10:002.00
1339B5/03/2020 19:006/03/2020 2:007.00
3479B29/02/2020 0:0029/02/2020 13:4013.67
5412B29/02/2020 0:003/03/2020 0:0072.00
5412B8/03/2020 0:0010/03/2020 10:5558.91
5412B10/03/2020 10:5810/03/2020 11:581.00
5412F10/03/2020 10:5510/03/2020 11:030.14
3288F29/02/2020 7:4529/02/2020 12:314.77
4735F29/02/2020 8:0029/02/2020 18:4410.72
5851B29/02/2020 15:2529/02/2020 19:514.44
456F2/03/2020 0:002/03/2020 3:563.95
456W2/03/2020 0:002/03/2020 3:563.95
774F2/03/2020 5:272/03/2020 6:301.06
774W2/03/2020 5:272/03/2020 6:301.06
Highlighted
Super User IV
Super User IV

Re: Merging multiple rows into one with conditions and Filtering issue

Hi,

I am not sure whom you are replying to but if it is me then i defeinitely do not understand your requirement.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors