Composite Models in Power BI
A composite model mixes more than one storage mode, or connects to a published semantic model and then adds your own tables. You keep the company’s Sales model. You add a local Targets file. You do not copy the warehouse into your PBIX.
Friends! The sales numbers already live in a published FreshBasket model. Why rebuild them? Because a target sheet on your laptop is the only new table. How? We connect to that semantic model, turn on changes, add Targets, and compare a 2025 target of 120 with 2025 sales of 100. The gap is 20, not all-years 150. Storage modes can differ. The question stays simple.
मित्रांनो! Sales numbers आधीच published FreshBasket model मध्ये आहेत. पुन्हा का बांधायचे? कारण लॅपटॉपवरील target शीट ही एकच नवी टेबल आहे. कसे? त्या semantic model ला जोडायचे, changes सुरू करायचे, Targets जोडायचे, आणि 2025 target 120 ची 2025 sales 100 शी तुलना करायची. फरक 20 आहे, सर्व वर्षांचे 150 नाही. Storage modes वेगळे असू शकतात. प्रश्न सोपा राहतो.
मित्रों! Sales numbers पहले से published FreshBasket model में हैं. फिर से क्यों बनाएँ? क्योंकि लैपटॉप की target शीट ही एक नई टेबल है. कैसे? उस semantic model से जोड़ें, changes चालू करें, Targets जोड़ें, और 2025 target 120 की तुलना 2025 sales 100 से करें. फ़र्क 20 है, सभी वर्षों का 150 नहीं. Storage modes अलग हो सकते हैं. सवाल आसान रहता है.
Quick answer
Vertical path:
- Get data → Power BI semantic models. Pick the published FreshBasket model.
- Choose Make changes to this model when you must add your own table.
- Get data from the Targets Excel file (City, Year, Target).
- Relate Targets to the city side only if one side of the key is unique.
- Mumbai sales 150. Mumbai 2025 target 120. Ahead by 30.
- Read Import vs DirectQuery vs Dual before you mix modes on purpose.
Published semantic model + your local table = composite model
Real example: sales there, target here
Published Sales (you do not retype these rows):
| City | Year | Channel | Amount |
|---|---|---|---|
| Mumbai | 2025 | Online | 100 |
| Mumbai | 2024 | Store | 50 |
| Pune | 2025 | Online | 40 |
Local Targets file:
| City | Year | Target |
|---|---|---|
| Mumbai | 2025 | 120 |
| Pune | 2025 | 50 |
What you show a manager:
- Mumbai sales, all years in the sample: 150.
- Mumbai target for 2025: 120.
- If the card is “2025 sales vs 2025 target”, Mumbai sales in 2025 are 100, target 120, short by 20.
- Say which year the card uses. Mixing 150 (all years) with 120 (2025 only) is a storytelling bug, not a composite bug.
- Pune 2025 sales 40 versus target 50 is short by 10.
The composite part is where the tables live. The honesty part is matching the year.
What do I need before this guide?
- A published semantic model you are allowed to use.
- Import vs DirectQuery vs Dual so Dual and DirectQuery are not new words.
- Course: Import vs DirectQuery vs Live connection.
Before and after (look at the tables first)
Before - Sales only. Nothing to subtract
आधी (Before) — फक्त Sales. वजा करायला काही नाही.
पहले (Before) — सिर्फ Sales. घटाने के लिए कुछ नहीं.
After - 2025 vs target. Short by 20 and by 10
नंतर (After) — 2025 विरुद्ध target. 20 ने आणि 10 ने कमी.
बाद में (After) — 2025 बनाम target. 20 और 10 से कम.
How to add your table
A composite model keeps the published semantic model and adds your local Targets table.
Composite model published semantic model ठेवतो आणि तुमची local Targets टेबल जोडतो.
Composite model published semantic model रखता है और आपकी local Targets टेबल जोड़ता है.
- In Desktop, Get data → Power BI semantic models.
- Pick FreshBasket. A live connection shows the remote tables. You cannot add columns yet.
- On the status bar or the Modeling tab, choose Make changes to this model. The file becomes a composite model.
- Get data → Excel → the Targets file. Check types: Target is a decimal, Year is a whole number, City is text.
- In Model view, relate Targets to a City (or Store) column that is unique on one side.
- If both City columns repeat, build a small local City table or use the remote City dimension if the relationship is allowed.
- Write a measure for the gap. Do not paste the target onto every sales row.
Match the year. Mumbai 2025 sales are 100 and the target is 120, so the gap is 20 — not 150 versus 120.
वर्ष जुळवा. Mumbai 2025 sales 100 आणि target 120, म्हणून फरक 20 — 150 विरुद्ध 120 नाही.
साल मिलाएँ. Mumbai 2025 sales 100 और target 120, इसलिए फ़र्क 20 — 150 बनाम 120 नहीं.
Target Amount = SUM ( Targets[Target] )
Gap to Target = [Target Amount] - [Total Sales]
For Mumbai 2025 only, the gap is 120 − 100 = 20 (target minus sales). A positive gap means you are short of the target. Say that in the card title: “Short of 2025 target”.
What composite does not mean
- It does not mean every table should be DirectQuery. Import is still the happy default for a small Targets file.
- A shared dimension in a mix of Import and DirectQuery is often set to Dual so it can work with both. That word is explained in the storage-mode guide. Do not flip every table to Dual “to be safe”.
- Some relationships between a remote model and a local table are limited. If the line is refused, fix the grain or ask the model owner for a proper column. Do not invent a many-to-many to force it.
- You still publish one PBIX. The sales data refreshes with the remote model. Your Targets table refreshes from the file or its new home.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| Cannot add a table | Still a pure live connection | Make changes to this model |
| Gap looks huge | Compared all-year sales with a 2025 target | Filter both to 2025 |
| Relationship refused | Key not unique, or mode limit | Fix the key; read the error |
| Everyone’s PBIX has a private copy of sales | You imported the warehouse again | Connect to the semantic model instead |
Ravindra Bagale's Tip
Interview line: “A composite model lets me keep a published semantic model and add a local table, such as targets. I match the year before I subtract.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “Composite model मला published semantic model ठेवून local टेबल, जसे targets, जोडू देतो. वजा करण्यापूर्वी मी वर्ष जुळवतो.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “Composite model मुझे published semantic model रखकर local टेबल, जैसे targets, जोड़ने देता है. घटाने से पहले मैं साल मिलाता हूँ.” समझ में आया?
Practice task
- Connect to a semantic model you may use, or simulate it with Sales plus a separate Targets file.
- Add Targets with Mumbai 120 and Pune 50 for 2025.
- Show 2025 Mumbai sales 100, target 120, short by 20.
- Write the card title so the year is obvious.
- Name the storage mode of Targets (Import) in one sentence.
Got it? Composite means the published model stays, and your target table joins the story. Match the year. Next: publish the report so someone other than you can open it. Let us go ahead.
समजलं का? Composite म्हणजे published model राहतो, आणि तुमची target टेबल कथेत येते. वर्ष जुळवा. पुढे: report publish करा म्हणजे तुमच्याशिवाय कोणीतरी उघडू शकेल. आता पुढे जाऊया.
समझ में आया? Composite यानी published model रहता है, और आपकी target टेबल कहानी में आती है. साल मिलाएँ. आगे: report publish करें ताकि आपके अलावा कोई और खोल सके. अब आगे बढ़ते हैं.
Frequently asked questions
What is a composite model?
A model that mixes storage modes, or a connection to a published semantic model plus your own tables.
Why not copy the sales data?
The company model already has it. You only add what is missing, such as targets.
What does Make changes to this model do?
It lets you add local tables. A pure live connection cannot.
Why is my gap wrong?
You compared all years of sales with a target for 2025 only.
What is Dual?
A storage mode for a shared dimension in a mix of Import and DirectQuery. See the storage-mode guide.
Course lesson?
Import vs DirectQuery vs Live connection, in the databases chapter.