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

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
v-gizhi-msft
Community Support
Community Support

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

Anonymous
Not applicable

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. 

 

 

 

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/
Anonymous
Not applicable

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

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

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])

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.