Transfer Result Table to next Model

[JoachimSA]

Hi everybody,

I am working on a 2 Step Problem where I would like to use the "Result" from Step 1 in the form of an aggregated table as starting point for Step 2.

To make it a little bit more concrete:
Step 1: Rather complex mapping of certain products on a vehicle data base. Result: Product Market in Volumes, Variants, Prices, over time
Step 2: Use the produkt market in step 1 and pick certain projects for a business case 

With the composite models I could Direct Query into the Product Market dataset that I publish to the PBI service. But I do not want the whole complex model. Only that one prepared calculated table would be enough. Is there a way to only query into one specific table in the data set ?
I am currently experimenting with exporting my data via DAX Studio in an excel file. This has the added benefit of potential versioning of market and business case just by using file names.
I am also working with data flows for data preparation, but for the product mapping and project picking I do prefer the power of DAX which is why I get my result as a calculated table which seems to be hard to move to the next "stage" of my process.
Any tips and best practises on this kind of data governance problem is highly welcome.

Thanks and best regards,

Joachim

[JoachimSA]

Okay so I figured out that I can leverage the new DIrect Query to Power BI Dataset Feature.

I tried to force a local copy of my relevant tables by more or less duplicating them as calculated tables.
One additional reason for this is that RELATED() does not seem to work properly when I try to connect local tables to my remote model directly.

With these duplicated versions I recreate the relationships from the original model and add new ones.
The Problem is that often times when I try to add new columns in this calculated column I get an error:

Unexpected Error (File "tmitransaction.inl", Row 129, Function "TMRelationshipResolver::IslandFromPartition").

I have tried googling it but other that disabling automatic calendar tables (did not help) I did not find anything.
Any suggestions on this ?

[JoachimSA]

Hi v-chenwuz-msft

thanks for your suggestion. Data Security is not much of a concern to me as of yet. 

I think i found the solution, though!
If i do not start from an empty pbix but already have some kind of local model (i.e. one table) then I can pick and choose which table are imported (including the relations between them).

On the left you see a small local table, on the right 2-3 tables from 6 different remote models that all have a lot more tables.

This is just what I wanted!
If I now want to add calculated columns to those new tables, the Microsoft Documentation says that I have to use "make changes to this model" which does not appear for me in this scenario. I assume that this is because I only loaded a part of the model ?

Wow...all theses different import modes are really complex to wrap your head around...live vs direct query, with or without local copy of the remote model. Anybody have a good resource that sums it all up ?

[GilbertQ]

Hi @JoachimSA 

You could always query the other dataset with a DAX query and then put that into a table in Power BI Desktop, which you could then use in your report?

[JoachimSA]

Hi GilbertQ,

thanks for your quick answer!
Intriguing thought...but how do I connect to another dataset without doing the live connection to a published PBI dataset bringing in all the tables ? Can I just query another dataset somehow with a specific DAX query ??  That WOULD be an amazing solution to my problem!

[v-chenwuz-msft]

Hi @JoachimSA,

As I know, DAX only works with the model already connected.

How about live connection to SQL server Analysis Service, but the whole model, all tables, will be connected to the power bi, the same as pubilshed PBI dateset.

If you are worried about data privacy, you can use RLS in the step 1 model or hide tables you do not want to dispaly to users. And connet to the model.

The table you want used in the next model , you can just copy it and enter data in the next model.

Best Regards

Community Support Team _ chenwu zhu

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

[JoachimSA]

Hi v-chenvuz-msft

thanks for your suggestion. Data Security is not much of a concern to me as of yet. <the problem is that there are really more than those 2 steps...maybe 5. And every time I use the lice connection to the next set it imports all the previus steps as well, even though I only need that one table! The Model becomes huge...hiding some of the tables is just cosmetics and when writing DAX the intellisense always suggests aaaall the measures and field names from the previous steps as well. It would be amazing to do something like a data flow but with the power of a data model with dax! There you can only have one table as result which can then be used in the next steps...