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
Solved! Go to 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.
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.
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.
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
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.
@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.