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 have this list:
This table is about testing. When a test is one, it is good. When it is two, it is bad. Every month there will come a new value for some rows (not every row). It depends on when it is tested. I want to count the total of good tested rows. Therefor it needs to find the last number in de row. If it is a one, then the whole row gets the value one in my calculation. If it has a one in july and later in august a two, then it needs to get a two. But if in september it will be good again it needs to switch to the one again and ignore the two. And so on for every month.
I now have:
Row 5 : a list of total tested distinct items in all the months
Row 6 + 7: filtered where the outcome is 1 or 3. This are good outcomes
Row 8: minus where the outcome is 2.
So I get in to trouble when there will be a 1 after the value of 2. It will count it double. I don't want that. The 1 AFTER a 2 or a 3 AFTER a 2 needs to be superior.
Can somebody help me?
I think you have bigger problems. Your data structure is not sustainable. You will want to unpivot your data and bring it into a form like
Item | Date | Result
Then it is trivial to use TOPN(1) or MAX() to find the latest result for each item.
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 |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |