Thread: Measure to identify based on criteria

1. Measure to identify based on criteria

I have a table in powerpivot showing invoices, and two calculated columns to identify Product A (1) or Product B (2)

 Invoice Product Units Price Calculated Column 1 Calculated Column 2 100 A 1 100 True False 100 B 1 50 False True 101 C 1 100 False False 101 A 1 100 True False 101 D 1 25 False False 102 B 1 50 False True 102 A 1 100 True False 102 C 1 100 False False

I want to be able to write a measure that will identify if each invoice has both "True" in calculated columns 1 and 2.

That way when I pivot it out, I can see the invoice number, products bought, total spend, and if a true/false if the invoice has both products A and B.

Any Suggestions?

2. Re: Measure to identify based on criteria

Code:
```=IF(
CONTAINS (
Table,
Table[Product],
"A"
) &&
CONTAINS (
Table,
Table[Product],
"B"
),
"Yes",
"No"
)```

3. Re: Measure to identify based on criteria

Thanks this works exactly how I asked!

I unfortunately now know I forgot to include details to my question because it did not work they way I wanted.

So the measure works perfectly if I pivot based on invoice number.

The problem, is that I need to get down to the products bought as well. But if I add products to the pivot table, the "Criteria" measure shows No for everything.

Is there a way to include products to the table, but the criteria only answer on the invoice level?

4. Re: Measure to identify based on criteria

Wrap the previous measure in a CALCULATE with second parameter as an ALLEXCEPT on Invoice.

5. Re: Measure to identify based on criteria

Sorry I took so long to reply, this works perfectly!