Flag all similar elements if at least one meets criteria

[Goncalo]

Hi everyone,

I have a table with invoices and purchase orders. I need to add a column that tells me that if at least one Invoice item has a PO number associated, all the items of that invoice show a Yes. If none has a PO number, then it should show No.

I leave below an illustration of the expected outcome:

Thanks in advance for you support!

Gonçalo

[vinieme12]

Add a measure As below

=If(count total <InvoiceNo> PoNumber),'Yes','No')

[Goncalo]

Hi Vineeth,

Thank you for your quick reply. I created the measure but now it is showing all invoices as Yes.

The PO number information that I use was obtained by checking if the PO field is filled in:

• If it is, then a subfield is used to retrieved part of the string to isolate only the PO number
• If is blank, then it shows a dash ("-")

Don't know if this may affect the formula you shared.

Best,

Gonçalo

[vinieme12 · Accepted solution]

Yes, that '-' is actually affecting the expression because it is supposed to count all non null values 

You can either replace the '-' to null() in your table in load script or we work around it like below 

=If(count {<PoNumber={"=Len(PoNumber)>2"}>}total <InvoiceNo> PoNumber),'Yes','No')

[Goncalo]

Perfect, it worked! Thanks