Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
curiouspbix0
Helper IV
Helper IV

How would you model this ?

How would you go about in modelling this requirement on power query or on dax

1 Import Dataset coming in every (A) 4 hours averaging 50 rows with city as primary field, need to store this in Power BI for a weeks data.

1 Direct Query Dataset (B) averaging 50 rows with city as primary field

1 Import Dataset coming in daily (C) averaging 8 million rows with city as primary field , this will go with the dataset B refresh.
There is more too but this will give a base.

 

Data from A,B,C all will go into the same report.


Merging A (Left join) to B (Left join) to C will blow up the dataset when merged as new on power query if am not wrong

I would still prefer Power Query given this could be still be optimized.

 

Or merge A to B resulting AB and then join with C on the data model on DAX ? 

Left outer is not a graphical join on DAX data model or a full outer that may also be required, could do DAX to create those tables but looking for simpler and optimal ways out here.

 

Shout out to the experts for Best practices here ...

 

 

1 ACCEPTED SOLUTION

@curiouspbix0 Yes sir



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

@curiouspbix0 , did not get it completely, but if it import or mixed model, you should able to create tables using

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

 

In DAX we use +0, or Show item without data for left join.

 

My question is more inline to an optimal model for the req at hand.

 

Not on how to do it literally on Power Query actually.

 

1. Join master tables on left to the transacational table on right with huge volume to write a new dataset on power query.

2. Keep the master table seperate to transactional table in power query and later join them in DAX data model ?

3. Other optimal options ?

 

@ImkeF,@Greg_Deckler, @edhans 

Any inputs from the experts ?

@curiouspbix0 I don't see any reason you need to merge in power query, it is typical dim and fact model.

 

Only thing I'm not clear what is the difference between A and B since both have City data?

 

If these two have different cities, I would recommend to append this in power query and make one city dimension and make sure it is unique (remove duplicate values)

 

From here everything will be super easy to work with and you will have the most effective model.

 

I hope it helps.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks Parry, there is more to the city table too which is not only master.
Like Cities would be listed by dates on the master table and could repeat so it is a more than typical master data table, was just trying to simplify for ideas.

 

I will stick to typical model principles then.

 

@curiouspbix0 totally, try to shoot for star schema, and there is no need to do the merging, you can still have city dimension as a separate table and relate to these two tables. there are many ways to achieve what you are trying to do but one thing is for sure, you don't need to merge or create a calculated table.

 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Many to Many join is what I was trying to avoid along with Bridge tables as the need is a LEFT OUTER actually.

 

So my options for left outer join  are 1. Use bridge table

                                         2. Write DAX

                                        

What is the best practice to enable multple left outer joins from masters to transactional ?

@curiouspbix0 Yes many-to-many can be expensive, doing bridge table is the way to go, and then any requirement to measure the data can be controlled by DAX.

 

I have developed very complex DAX measures on very complex data models to meet some complicated business requirements, of course sometimes you need to check the performance of your measures to make sure measures are optimized. 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

So you would rather use bridge tables & Dax to Power Query processed dataset that would result one table with master and transactional together which is obviously better on report refresh visual runtimes with a trade off on backend ETL on Power query.

@curiouspbix0 Yes sir



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.