Forum Discussion

smpa01's avatar
smpa01
Community Champion
4 years ago
Solved

DAX query on different DB but same SSAS server

lbendlin TomMartens GilbertQ bcdobbs parry2k d_gosbell  I have not learnt who are the SME on this and if you feel that there is someone else who can help me on this please don't hesitate to tag. S...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    I don't think you can do it directly like you're trying. 

    The closest you could get is to follow parry2k suggestion and drop both models into a composite model and publish to premium workspace. To an external xlma connection that then behaves as a single model on which you could run your natural join.

     

    Would be amazing if you could query directly like you suggest in SSMS or DAX studio but I suspect given the work that microsoft have had to put in to make composite models function I'm not sure it is. Happy to be corrected though.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

This doesn't take advantage of them being on the same server, but you can load both tables and merge them in Power Query along these lines:

let
    Table1 = AnalysisServices.Database(Workspace, DB1, [Query="EVALUATE Table1"]),
    Table2 = AnalysisServices.Database(Workspace, DB2, [Query="EVALUATE Table2"]),
    Merged = Table.NestedJoin(Table1, "custID", Table2, "custID", "Table2", JoinKind.LeftOuter)
in
    Merged

I know this isn't exactly what you're looking for. It's not a Live or DirectQuery connection and isn't fully evaluated on the server side but might still be useful in occasional situations.