Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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"))
Proud to be a Super User!
Try this MEASURE
Measure = ISEMPTY ( EXCEPT ( { "AA", "DD" }, VALUES ( FactSales[Product] ) ) )
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,
Hi @chudson
As a calculated column, you can use
Column = ISEMPTY ( EXCEPT ( { "AA", "DD" }, CALCULATETABLE ( VALUES ( FactSales[Product] ) ) ) )
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,
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"))
Proud to be a Super User!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |