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

chudson 

 

Try this MEASURE

 

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

    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'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,