Forum Discussion
Helper IVDAX expression looking up multiple values with a unique search value
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 |
Hi chudson ,
you could try creating a calculated summary table with DAX using a formula like
Summarized Table = ADDCOLUMNS(
You could also accomplish this using power query grouping.
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"))Richard
5 Replies
- Zubair_Muhammad
Community Champion
Try this MEASURE
Measure = ISEMPTY ( EXCEPT ( { "AA", "DD" }, VALUES ( FactSales[Product] ) ) )
chudsonThe 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] ) ) ) )
