Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

Need Help

6 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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:

    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:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.