Excel formula into DAX to create calculated column...

NeilJustice · ‎08-31-2023

I have a table with thousands of lines as in the example below.

I want to be able to group the information by InvoiceId column (A) then look at the value in (C) to return the expected result in column (E), but its proving somewhat difficult to write a DAX statement to provide this information in a new column in power bi, or power query in power bi. Note that in (A) there are multiple id numbers and multiple variations in (C), one rule is that when there are multiple lines with the same invoiceId and PO or No PO in column (C) then PO will always override No PO. If it is just a single or multiple line in (A) with the same value in (C) then it is true to return that value. Its only when i hit multiple variations in (C) to the same Id in (A) where i cannot get the expected result. I have a formula in excel that seems to work okay in excel table, but cannot convert to a DAX formula. I have provided this below if it helps please?

=INDEX($C$1:$C$1000,MATCH(MIN(IF(($A$1:$A$1000=A1)*($C$1:$C$1000<>"No PO"),$D$1:$D$1000)),($D$1:$D$1000)*($A$1:$A$1000=A1)*($C$1:$C$1000<>"No PO"),0))

technolog · ‎09-24-2023

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.