DAX expression looking up multiple values with a unique search value

[chudson]

For simplicity sake, I have a factsales table and another order table that contains a unique order number per row.  My Dummy tables are below and basically what I want to do is lookup in my factsales table and return a YES if the order number contains multiple specific product code/number (in this case if the order number contains AA & DD return Yes, if not return no)

Can anyone help with a dax expression to do this?

Fact Sales (multiple rows per order)

Ordernum	Product
1	AA
1	BB
1	CC
2	DD
2	DD
2	AA
2	AA
3	CC
3	DD
4	BB
5	AA
5	DD

Summarize order table trying to return Yes when referencing the fact sales table if contains AA & DD on the same order.  My real data is much more complex but just trying to see what DAX expression could be used

OrderNum	Return
1	No
2	Yes
3	No
4	No
5	Yes

[Zubair_Muhammad]

@chudson 

Try this MEASURE

Measure =
ISEMPTY ( EXCEPT ( { "AA", "DD" }, VALUES ( FactSales[Product] ) ) )

[chudson]

Hi @Zubair_Muhammad 

The measure seems to work but I'm unable to use it as a filter or calculated column.  Is there a way to expand this measure to allow as a filter or create a calculated column?

Thanks,

[Zubair_Muhammad]

Hi @chudson 

As a calculated column, you can use

Column =
ISEMPTY (
    EXCEPT ( { "AA", "DD" }, CALCULATETABLE ( VALUES ( FactSales[Product] ) ) )
)

[chudson]

Hi @Zubair_Muhammad 

I tried that version in my model but am getting True values only.  Do I need to have some sort of reference to the order number as well?

Thanks,

[richbenmintz · Accepted solution]

Hi @chudson ,

you could try creating a calculated summary table with DAX using a formula like

Summarized Table = ADDCOLUMNS(
SUMMARIZE('Details', Details[Ordernum], 
"Products", CONCATENATEX('Details', 'Details'[Product], ",")), 
"Return", if(FIND("AA",[Products], 1, 0) >0 && FIND("DD",[Products], 1, 0) >0 , "Yes", "No"))

You could also accomplish this using power query grouping.

Richard