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.
Dear all,
I need your support in writing a DAX formula.
Situation
I have a table containing a column "PurchaseOrderDocumentReference" there are several confirmations in each Purchase Order. So, I want to filter the latest confirmation of the column. How to do that?
Best Regard
Solved! Go to Solution.
Okay,
Now it depends if you need calculated column or measure, but I will cover both.
1. You need to create new column, where you extract the last digit. So you should have two columns. One with only pure PO and second with "version":
The best way how to do this is in Power Query, for example with extract options:
2. Once you have your two columns, you can create conditional DAX column, which defines last version. Formula looks like this:
At this point, you can use this column for filtering you report. You just need to check "Latest".
3. In case you want to create a measure, you can now use this column for measures. Let's say each of my order has also value. If we want to sum value only for latest PO, we can do it:
Last Value for POs = CALCULATE(SUM('Table'[Value]),'Table'[Last Version] = "Latest")
my highlight is an example of how the PO number duplicates and the difference is only the last digit of each rows
So if I understand well:
You want to filter purchase orders with the highest number at the end? So for example: PO-00000-3 and PO-00010-3?
Yes, Correct
Okay,
Now it depends if you need calculated column or measure, but I will cover both.
1. You need to create new column, where you extract the last digit. So you should have two columns. One with only pure PO and second with "version":
The best way how to do this is in Power Query, for example with extract options:
2. Once you have your two columns, you can create conditional DAX column, which defines last version. Formula looks like this:
At this point, you can use this column for filtering you report. You just need to check "Latest".
3. In case you want to create a measure, you can now use this column for measures. Let's say each of my order has also value. If we want to sum value only for latest PO, we can do it:
Last Value for POs = CALCULATE(SUM('Table'[Value]),'Table'[Last Version] = "Latest")
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.