Forum Discussion
How to track wrong sequence by date
Hi all,
I am fairly new to PowerBI and I am stuck on a requirement I was working on.
I have a simplified version of data set I am working on.
The column "Step No." is supposed to be in incremental order per date, for each order ID.
I want to create a card that shows the number of times the steps were not in sequential order.
Result:
Here, the row shown below is in the wrong order.
I would appreciate any help to find a solution to this problem.
Thanks in advance.
2 Replies
Anonymous ,
I would handle the problem in the following manner.
Create a new Calculated Column using RANKX. This will give you the "Correct Order":
RankbyOrderID = VAR _OrderID = [Order ID] VAR _Result = RANKX( FILTER('WrongSteps', WrongSteps[Order ID] = _OrderID ), WrongSteps[Created Date], , ASC ) // Rank in Ascending Order by Date RETURN _ResultThis produces the following:
Category ID Product ID Order ID Step No. Created Date RankbyOrderID 4 104 1004 3 14/10/2020 1 4 104 1004 1 15/10/2020 2 4 104 1004 2 16/10/2020 3 From here, if the Rank Column does not equal your Step No., then the sequence is out of order.
Please note that this method will result in a count of 3, not a count of 1, because all 3 steps are out of order.
Alternatively, you can do a DistinctCount on OrderID to tell you number of orders that are out of Step. This will give you a count of 1.
Hope this helps.
Regards,
Hi rsbin,
Thank you for the approach, but the issue here is, the data I have shown is just dummy data and the column "Steps No." is not of the values 1, 2, 3 etc. The steps No. would have values like "1. Step1", "2. Step2" etc.
