Connecting to a semantic Model and then importing data

[PPStar]

Hello. 

I have a semantic model published in a premium workspace. End users are restrcted from modifying this model, but can connect to using a Live Connection, or Direct query when using it as a composite model. 

When they use it as a Composite Model, they are limited in functioanlty in DAX.  I am aware they cant perform any power query on the model as its connect via Direct Query. 

I was under the impression users could change the storage mode to Import - meaning they import the data into the report and can use use DAX functions, such as RELATED. 

However, the storage mode is greyed out. 

Can anyone shed any lght on this?. I want disable the ability for users to modify the model in my premium workspace, but want to give them access to connect to it from Power BI, and then merge their excel files with this model (composite model) and perform DAX functions in their report. 

Thanks 

[v-junyant-msft]

Hi @PPStar ,

Some of DirectQuery's limitations remain even in composite mode:

Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Regarding the change of storage mode, it can be changed only once per table. Because you are connecting to semantic model by Live Connection, and then change to Composite Model, you can't change the storage mode again because it has been changed from Live Connection to DirectQuery, which includes the change of storage mode once. This has already included a storage mode change, so you can't change the storage mode again. That's why the storage mode is grayed out.
Please refer to the following documentation, which may be helpful:
Use composite models in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other member find it more quickly.

[PPStar]

Hi Dino, 

Thanks for your response. 

When i initially connect to the semantic model using a Live Connection - at that point, the solution is not a composite model - so i am correct in saying a Live Connection cannot be changed to import - becuase it a live Connection. 

Im slighlty confused as to when you can change the storage mode to Import... Or does it just not work when connecting to semantic models?

[v-junyant-msft]

Hi @PPStar ,

After testing, it is not possible to change the storage mode in Live Connection mode itself. If you are switching from Live Connection mode to Direct Query mode, it still won't do the conversion either. You can change the storage mode if you connect directly using Direct Query mode.

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

[PPStar]

how can i connect power bi desktop to my semantic model using Direct Query first?

[v-junyant-msft]

Hi @PPStar ,

Sorry but after my test, you can't connect to semantic model using Direct Query.

Best Regards,
Dino Tao

[v-junyant-msft]

Hi @PPStar ,

Sorry I have to correct myself, you can connect to semantic model using Direct Query, but the overall model of your report must be a hybrid model. After testing, you have to connect to excel in import mode before you can connect to semantic model using Direct Query, but even in this case you are still not allowed to change the storage mode!
Manage DirectQuery connections to a published semantic model - Power BI | Microsoft Learn

Best Regards,
Dino Tao

[AlexisOlson]

I don't think this is intended to be possible. However, while DirectQuery does limit DAX functionality, you should still be able to do most things, even if not exactly the same way.

[PPStar]

Any idea on how to use the RELATED function.

I would like to 'blend' data from the semantic model with a local excel file.

When I mean blend, I want to merge/append or get a column from the semantic model to appear as a column in Mt excel table 

[AlexisOlson]

Some functions you just can't use. If you give me an example of what you're doing with RELATED, I might be able to find a workaround that doesn't use it.

[PPStar]

Ok imagine you have a semantic model called Table A you connect to which have a a column called ID.

Then you import an excel file (called Table B) which also has a ID column and Description column.

You create a relationship between the two tables.

I want to add thr description to every ID column in Table A where the relationship matches.

So if table A and Table have a 1:M relationship.

can we add a new column  to Table A which is the description for each ID that matches.

hope that makes sense

I would like to use the RELATED function to add a new column 

[AlexisOlson]

I see. What you can't do is add a column to a DirectQuery table that depends on tables outside of the source.

As we can see, LOOKUPVALUE doesn't work either:

That said, with this relationship, you can still use the TableB[Description] column in your report as if it were a column in TableA. Not as clean as working with a single table, but should still be functional. If you use the description column in a measure, then that measure can still live in TableA.

[PPStar]

I appreciate your workaround. Its not a clean workaround, but with that said - i dont thikn having any type of workaround will suffice.  I think i need to go back to the drawing board. Thanks again