create a table visual based on 2 tables (no calculated columns, tables or relationships)

[OlegV]

Hello,
I have two power bi dax tables. 

I need to create a table visual. In which the first column is 'Sales Orders'[Sales Order], the second column is 'WiPJobs'[Job].

I cannot create new caculated tables, calculated columns or relationship between these tables.

I tried to use lookup and selected value functions, but there are several 'WiPJobs'[Job] for one 'Sales Orders'[Sales Order].

Could you please advise any solution. Thank you.

Here is pbix file, on my Onedrive  sample.pbix

or codes

SalesOrders = DATATABLE(
"Sales Order",STRING,

{
{"A"},
{"B"},
{"C"},
{"D"}
}
)

WIPJobs = DATATABLE(
"Sales Order",STRING, "Job",STRING,
{
{"A","A1"},
{"A","A2"},
{"B","B1"},
{"C","C1"},
{"C","C2"},
{"C","C3"},
{"D","D1"}
})

[Greg_Deckler · Accepted solution]

@OlegV Try:

Measure = 
    VAR __SO = MAX('SalesOrders'[SalesOrder])
    VAR __Result = CONCATENATEX( FILTER( 'WIPjobs', [SalesOrder] = __SO ), [Job], ", " )
RETURN
    __Result

[v-tangjie-msft · Accepted solution]

Hi @OlegV ,

Thank you @Greg_Deckler  for the quick response and solution. In addition to creating a measure, we can also create a new table.

NewTable =
ADDCOLUMNS (
    'SalesOrders',
    "Job",
        CONCATENATEX (
            FILTER ( 'WIPJobs', [Sales Order] = EARLIER ( 'SalesOrders'[Sales Order] ) ),
            [Job],
            ","
        )
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Greg_Deckler · Accepted solution]

@OlegV Try:

Measure = 
    VAR __SO = MAX('SalesOrders'[SalesOrder])
    VAR __Result = CONCATENATEX( FILTER( 'WIPjobs', [SalesOrder] = __SO ), [Job], ", " )
RETURN
    __Result