Combine two columns from different tables in SSAS Tabular with DAX

[pjmanuel]

Hi All,
I try to Combine two columns from different tables in SSAS Tabular with DAX. I had the follow code to get the two columns

=VAR A = CALCULATETABLE( SUMMARIZE(StockDetail,StockDetail[ProductCode]),FILTER(RELATEDTABLE(Product),Product[code]))
VAR B =CALCULATETABLE( SUMMARIZE(Product,Product[code]),FILTER(RELATEDTABLE(StockDetail),StockDetail[ProductCode]))
VAR result = A
RETURN result

At moment I only have one column, I would like to have a bridge table with the two columns "StockDetail[code]" and "product[code]".

thanks,

pm

[v-yalanwu-msft · Accepted solution]

Hi, @pjmanuel ;

Try it.

Filtered Bridge = 
VAR _a= DISTINCT(UNION(VALUES(A[Itema]),VALUES(B[ItemB])))
var _add=ADDCOLUMNS(FILTER( _a,[Itema]<>BLANK()),"Item A",CALCULATE(MAX('A'[Itema]),FILTER('A',[Itema]=EARLIER([Itema]))),"Item B",CALCULATE(MAX('B'[ItemB]),FILTER('B',[ItemB]=EARLIER([Itema]))))
return SUMMARIZE( _add,[Item A],[Item B])

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @pjmanuel ;

Try it.

measure =
VAR A =
    SUMMARIZE ( StockDetail, StockDetail[ProductCode] )
VAR B =
    SUMMARIZE ( Product, Product[code] )
RETURN
    SUMMARIZE ( UNION ( A, B ), [code] )

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pjmanuel]

Hi Yalan Wu,
Great your help, but I yould like to see the two columns side by side.Union gives me only one column. And by the way you can't put in the same column a stocklineID   and a productID

Cheers

[Momie2005]

can you check this one? https://www.tallan.com/blog/2017/02/09/analysis-services-tabular-many-to-many-relationships-bridge-t...

[pjmanuel]

Hi, amitchandak
At moment I'm using SSAS tabular. and I try to create a bridge table between
stockdetail table and Product that have the stockdetailID and productID.

[amitchandak]

@pjmanuel, Are you using a live connection? Then I doubt that will work in power bi.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.