Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chudson
Helper IV
Helper IV

DAX 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)

OrdernumProduct
1AA
1BB
1CC
2DD
2DD
2AA
2AA
3CC
3DD
4BB
5AA
5DD

 

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

OrderNumReturn
1No
2Yes
3No
4No
5Yes
1 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


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

@chudson 

 

Try this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

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,

Hi @chudson 

 

As a calculated column, you can use

 

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

Regards
Zubair

Please try my custom visuals

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,

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


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.