Forum Discussion

chudson's avatar
chudson
Icon for Helper IV rankHelper IV
7 years ago
Solved

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...
Need Help
  • richbenmintz's avatar
    richbenmintz
    7 years ago

    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
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

Hi chudson 

 

As a calculated column, you can use

 

Column =
ISEMPTY (
    EXCEPT ( { "AA", "DD" }, CALCULATETABLE ( VALUES ( FactSales[Product] ) ) )
)
chudson's avatar
chudson
Icon for Helper IV rankHelper IV
7 years ago

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's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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