Compare two rows in a table in PowerBI

Question

Need your assistance for the below query.

I have one table that is fetched in PowerBI as below

ID  MessageId   Eventtype   Timestamp       SystemTypeId
1   101             Pack    14/02/18 00:00      ABC
2   102             Unpack  14/02/18 00:00      ABC
3   103             Unpack  14/02/18 01:00      ABC
4   104             Pack    14/02/18 01:00      ABC
5   105             Unpack  14/02/18 01:00      ABC
6   101             Pack    14/02/18 00:00      XYZ
7   102             Unpack  14/02/18 00:00      XYZ
8   104             Pack    14/02/18 01:00      XYZ
9   105             Pack    14/02/18 01:00      XYZ

I want to compare the rows with the same MessageID.

For example: I am comparing MessageID 101, then all the values like Eventtype, Timestamp, System are matching for both the systems i.e. ABC and XYZ.

But for the MessageID 105 the value for Eventtype is not matching or both the systems i.e. ABC and XYZ hence this message will not be counted as a MATCH message. Finally i want to show it on the dashboard as

14/02/18 00:00            14/02/18 01:00
Count of Messages        Count of Messages
FSS3         OER          FSS3       OER

2              2          3           1

I require a flag across the above table that will help me to obtain the above result in PowerBI.

Can you please assist me with the same.

Thank you so much in advance.

cddbldot cddbldot · Accepted Answer · 2018-02-14T19:20:38

This should give you a calculated column with the info whether or not there is a matching row. It implies there can be no match from the same system, otherwise you will have to add a check for the System Type as well and exclude those results (will be hard to link possible matches, though).

MatchingRow =
VAR tid = table[ID]
VAR mid = table[MessageId]
VAR evt = table[Eventtype]
VAR tim = table[Timestamp]
RETURN IF(CALCULATE(COUNTROWS(table), ALL(table), table[ID] <> tid, table[MessageId] = mid,
table[Eventtype] = evt, table[Timestamp] = tim) > 0, "match", "no match")

I believe you can take it from here, otherwise you will have to explain FSS 3 and OER.

Compare two rows in a table in PowerBI

1 Answers