Creating a Composite Model Against a Power BI Dataset

Learn how to create a composite model against a Power BI dataset, also known as (DirectQuery for Power BI datasets and Azure Analysis Services) or DQFPBIDAAS for short. 😅

Composite Models against a Power BI Dataset allows you to import tables from other data sources and combine it with your Power BI dataset.

RELATED CONTENT 🔗

BECOME A CHANNEL MEMBER 🎉

LET'S CONNECT! 🧑🏽‍🤝‍🧑🏽 🌟

VIDEO CHAPTERS 🎥
0:00 - Start of Video
0:49 - Content Intro

HAVENS CONSULTING PAGES 📄

EMAIL US AT 📧

#PowerBI #powerplatform #microsoft #businessintelligence #datascience #data #dataanalytics #excel #powerapps #datavisualization #dashboard #bi #analytics #powerquery #dax #modeling #compositemodels #directquery

[richardbachmaier]

Hello Reid,
Thank you for the helpful video!

I have a question about the following scenario:
- There is a dataset A with RLS.
- I create a second dataset B, which connects to A via live connection as shown in the video.
- I would like to create further, more granular RLS roles in B, which is not possible in a dataset with lineage

To extend the RLS roles anyway, I have the following approach:
- I create a composit model consisting of A (connection via. direct query) and a helper table.
- I create RLS roles for the helper table.
- I create a relationship between the helper table to the corresponding table from A in order to pass on the RLS roles to the whole model.

However, filters in the helper table are not passed on to A despite the relation. Do you have an idea, how my approach could work? Is there a better approach to create a filter for a dataset with lineage based on RLS roles?

[premcst]

Thanks Reid .. Can we import a PBI dataset and Make another PBI dataset as Live connection / direct query ??

[danielsiffredi]

Thanks for the info! This is great news !!! Would you confirm that is now possible to create new measures towards the live connection? Let’s say we create a new table to store measures - would it be possible now to create measures?

[sharmitasahaporshia]

Super excited about starting to use Composite Models - 1 directQuaery dataset + Sharepoint List. However the formatting of measures is getting lost in the new model. Any suggestion?

[ErikPgh]

Great intro... When you have some basic models with Datesets that already have Date Tables embedded in their distinct model what is the best practice for bringing into the aggregated model. Especially if there "USERELATINSHIP" links in the lower level model. Would love to see a video on this if possible.

[emilydonohoe]

Does this still work when publishing the report to an app in Power BI Online? I tried this and once it's published it doesn't allow for this mixed approach as there is no gateway for the import data

[nicolopasini]

Hi Reid, thanks for the cool video! This is really nice and helpful for my usecase, but there's a topic I'd like address with you: is it possible to publish this report (composite model, with mixed storage modality, import + DirectQuery) and its dataset on PBI service and then refresh the dataset itself directly on PowerBI service?

The point is that it seems that when you publish it on PBI service the system does not allow you to refresh the dataset anymore, as it says the following: "Refresh is not supported for datasets with a calculated table or calculated column that depends on a table which references Analysis Services using DirectQuery."

Do you think there is a work around for this if I have a Pro license? I need this dataset to be refreshable directly on the service (and not only on PBI desktop only) because other datasets and reports depend on it. I think a solution could be to import the tables I need from the PBI dataset instead of using a DirectQuery, but it seems like importing from a PBI dataset is not allowed...

Thanks in advance for your help! Please let me know if you need additional info to better explain my case :)
Nic

[hemantagarwal]

Hi Reid!

Can you make the connection of the Product Table(Import) with the Sales and DAX tables simultaneously ?
Main question: can a import table have relationship with multiple direct query tables in the mixed storage model?

[xiaoshi]

Thanks for the video. Do you know if it is possible to append the tables in this mixed mode?

[santoshhmt]

Hi what about applying RLS. Can you please make video on applying RLS on composite models. It will be very helpful

[HachiAdachi]

Thanks, Reid. Now, would you use the file with the composite model as the 'Data Model' file and create a separate 'lean report' file live-connected to the data model file?

[PJogo]

Hello Reid!
Thanks for your video, quite useful :)

One question: imagine I have a dataset A with a RLS that limits the access to person B and C. I build a composite model based on this one + one excel file I have.
What will happen to the data I am showing into the composite model that comes from the dataset A? Will be filtered, not show at all or the RLS looses its action?

Thanks for your help :)

[panxostyle]

Hi Reid, thanks a lot for your video. I trying use this feature and I do not know if is possible to made some changes in the dataset, I mean transform data with power query. Because apparently is not possible.

[Pklot]

Hi Reid, is there a way to solve the limit where only 1M rows can be loaded from a DirectQuery? I wanted to use composite models to re-use large tables (>1M rows) in other models, so we don't have to load them into the service multiple times, but now I can't calculate any measures on these, due to the data size limit. How would you approach this?

[fj]

Thanks for this! however after I connect with my power bi dataset, there's no 'make changes to this model' option. I ticked the preview features for directquery but still not happening.. do you know why??

[jjhmr]

If you make changes in the original (main) dataset (DAX measures, dimensions included or not and hidden or not, power query steps, etc.), will that be applied automatically to the composite model, or do you have to create the composite model again based on the updated main dataset? It seems it is the latter in our case, and this is unfortunate.

[corentincorcelette]

Hello :)

Can we use composite model based on dataset power bi + incremental refresh and one excel file ? Or is there a limitation for incremental refresh ?

[mwaltercpa]

Thanks Reid, just the info I needed :)

[blackpink_singing]

If I have added table from ssas with live connection and want to import tables from sql then would it possible to add and set relationship?

[yyymmmddd]

If Composite model is limited to Direct queries only it makes it practically useless.