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.
I want to analyse the reason some transactions fails. I have two datasets that are relevant for this. One dataset containing the following:
Code | Description |
1 | Transaction completed |
2 | Canceled by customer |
3 | Failed due to XXX |
4 | Failed due to XXX |
5 | Failed due to XXX |
6 | Failed due to XXX |
7 | Failed due to XXX |
In the other dataset that relates to the data above I have this info:
Session | Timestamp | Machine-ID | Transaction status | Status code transaction |
1000XXX | XX.XX.20 | 120XX | Completed | [1] |
1000XXX | XX.XX.20 | 120XX | Failed | [2, 7] |
1000XXX | XX.XX.20 | 120XX | Completed | [1] |
1000XXX | XX.XX.20 | 120XX | Failed | [3, 6] |
1000XXX | XX.XX.20 | 120XX | Failed | [3, 4, 7] |
1000XXX | XX.XX.20 | 120XX | Failed | [2, 6] |
I want to get some insight into what is the cause of the failed transactions using similar data as above I get results like this in my report:
As you can see PowerBI does not sum every failed transaction with status code [2] together, but it sums all the failed transactions with the same status code transactions (i.e. [3, 7]). For some transactions there are only one reason for the transaction failing, as you can see the most repeating status code on failing transactions is [3].
What I would like to do is to edit my data so that I can get the sum of for example [2] alone and [3] alone.
Solved! Go to Solution.
If you remove the '[' and ']' from the Status code transaction column (using 'replace values' in Power Query).
Then Split the column (by comma).
Then select the first 4 columns (the non-status columns) and choose 'Unpivot other columns' from Transform menu.
This gives the columnar form of data that powerbi likes.
You can relate the two tables via 1-to-many on Code and Value.
In the visual in Report view, I think you plot the Code v Count of Value. Use a slider on Code from the dimension table to filter Status codes.
If you remove the '[' and ']' from the Status code transaction column (using 'replace values' in Power Query).
Then Split the column (by comma).
Then select the first 4 columns (the non-status columns) and choose 'Unpivot other columns' from Transform menu.
This gives the columnar form of data that powerbi likes.
You can relate the two tables via 1-to-many on Code and Value.
In the visual in Report view, I think you plot the Code v Count of Value. Use a slider on Code from the dimension table to filter Status codes.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |