Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Concat one table to another table

Data

 

In Data table I have two columns are Item and supplier code, The item column and supplier code contain/stored as a number and text, both column contain duplicated/repeated.

 

Report

 

In report table I have a unique item column (Not duplicated) . The item column contain/stored as a number and text.

 

Result

               

I am looking for supplier code within the same column from data table into report table according to the item.

 

If item not available in data table then return “NA” in report table according to the item.

I am applying the below mentioned DAX formula in report table.

 

Supplier code =
VAR _table =
    CALCULATETABLE (
        VALUES ( DATA[Supplier Code] ),
        FILTER ( ALL ( DATA ), DATA[ITEM] = EARLIER ( REPORT[ITEM] ) )
    )
RETURN
    CONCATENATEX ( _table, DATA[Supplier Code], "," )


How can I create a measure to achieve same result in report table. I am trying to apply the above mentioned formula in measure but it's not working. Can you please explain why new calculated column formula not supporting in mesure? 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but if you want to create a measure, please try the below.

     

    Supplier code measure = 
VAR _table =
    CALCULATETABLE (
        VALUES ( DATA[Supplier Code] ),
        FILTER ( ALL ( DATA ), DATA[ITEM] = MAX ( REPORT[ITEM] ) )
    )
RETURN
    CONCATENATEX ( _table, DATA[Supplier Code], "," )

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but if you want to create a measure, please try the below.

     

    Supplier code measure = 
VAR _table =
    CALCULATETABLE (
        VALUES ( DATA[Supplier Code] ),
        FILTER ( ALL ( DATA ), DATA[ITEM] = MAX ( REPORT[ITEM] ) )
    )
RETURN
    CONCATENATEX ( _table, DATA[Supplier Code], "," )
    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Jihwan_Kim,

      Thanks for your solution. 

      Can you please advice why using max function in measure instead of earlier?

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your message.

        I think the reason is that it is not in the row context, so earlier is not the option.... this is what I think.

         

        And this is not related to this case, but I usually use VAR instead of earlier function. And then, in my case, it is easier to understand the formula.

         

        I hope this helps.