Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to find a recursive pattern between a group of tickets. This pattern implies that a ticket is assigned from one group (G1) to another group (G2) but in the next itteration it is assigned to the starting group (G1). The pattern would be G1-->G2, G2-->G1.
When this happens I should obtain the label “ANOMALY” in the new calculated column “Anomalies”.
Each row is a transaction. Rows (transactions) are grouped by ticket ID (ID).
I have tried using EARLIER function but it does not work
ID | Initial Group | Final Group | ANOMALIES |
A1 | G1 | G1 | OK |
A1 | G1 | G1 | OK |
A1 | G1 | G2 | ANOMALY |
A1 | G2 | G1 | OK |
A1 | G1 | G3 | OK |
A2 | G1 | G1 | OK |
A2 | G1 | G1 | OK |
A2 | G1 | G2 | OK |
A2 | G2 | G3 | ANOMALY |
A2 | G3 | G2 | OK |
A2 | G2 | G1 | OK |
A2 | G1 | G2 | ANOMALY |
A2 | G2 | G1 | OK |
A2 | G1 | G1 | OK |
A2 | G1 | G1 | OK |
A3 | G1 | G2 | ANOMALY |
A3 | G2 | G1 | OK |
A3 | G1 | G1 | OK |
A3 | G1 | G1 | OK |
A3 | G1 | G1 | OK |
Thanks in advance
hi, @Ley,
I assume that you either have date column in your table, or that the order of the rows in your source in the order of occurence and can be indexed.
I have taken the data you have provided and added an index to the table in Power query. I then created this calculated column:
AnomaliesDAX =
VAR _nextIndex = 'Table'[Index] + 1
VAR _nextInitialGroup =
CALCULATE (
SELECTEDVALUE ( 'Table'[Initial Group] );
FILTER ( ALL ( 'Table' ); 'Table'[Index] = _nextIndex )
)
VAR _nextFinalGroup =
CALCULATE (
SELECTEDVALUE ( 'Table'[Final Group] );
FILTER ( ALL ( 'Table' ); 'Table'[Index] = _nextIndex )
)
RETURN
IF (
'Table'[Initial Group] < _nextInitialGroup
&& 'Table'[Initial Group] = _nextFinalGroup;
"Anomaly";
"OK"
)
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi,
I have tried the formula, but it does not work properly. There are some mistakes:
- In order to consider like an anomaly, we have to group tickets and compare rows that belong to the same ticket. I have found cases that there is recursivity (from G1 to G2 and from G2 to G1) but in DIFFERENT tickets. So this should not be an anomaly
- There can not be an anomaly when a ticket has an unique transition. I have detected anomalies in this cases
- When I filter by ticktes anomalies does not apear properly
An example:
Any idea about waht could be grong?
Thanks
Yes, there were a couple of weaknesses in my code, so you can replace it with this:
AnomaliesDAX =
VAR _currentID =
CALCULATE ( SELECTEDVALUE ( 'Table'[ID ] ) )
VAR _currentIndex =
CALCULATE ( SELECTEDVALUE ( 'Table'[Index] ) )
VAR _nextIndex =
CALCULATE (
MIN ( 'Table'[Index] );
FILTER (
ALL ( 'Table' );
'Table'[ID ] = _currentID
&& 'Table'[Index] > _currentIndex
)
)
VAR _nextInitialGroup =
CALCULATE (
SELECTEDVALUE ( 'Table'[Initial Group] );
FILTER (
ALL ( 'Table' );
'Table'[Index] = _nextIndex
&& 'Table'[ID ] = _currentID
)
)
VAR _nextFinalGroup =
CALCULATE (
SELECTEDVALUE ( 'Table'[Final Group] );
FILTER (
ALL ( 'Table' );
'Table'[Index] = _nextIndex
&& 'Table'[ID ] = _currentID
)
)
RETURN
IF (
'Table'[Initial Group] = _nextfinalGroup
&& 'Table'[Final Group] = _nextInitialGroup
&& _nextInitialGroup <> _nextFinalGroup;
"Anomaly";
"OK"
)
There is one difficulty in the data from your original post, though:
A2 | G3 | G2 | OK |
A2 | G2 | G1 | OK |
A2 | G1 | G2 | Anomaly |
A2 | G2 | G1 | OK |
With these data it is possible to make use of greater than/less than to decide if a row is an anomaly or not. But with data you just posted that is not an option, and no direct way if separating row 2-row 3 and row 3-row 4. The code I posted above will mark them both as anomalies. It might be a possibility to add columns that identify the order of the groups, and make use of that in order to identify an anomaly.
Another option could be to identify all combinations of anomalious combinations and search for that.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |