Tricky Snowflake Schema Calculations in Power BI || A Step-by-Step Guide

Related and RelatedTable in DAX - • Related and RelatedTab...
In this video, we will explore the nuances of working with snowflake schemas and creating calculations between tables.
While the results may initially seem correct, they can often be incorrect due to the nature of the snowflake schema. We discuss how to validate and fix these calculations, ensuring accurate data analysis.
#datamodeling #powerbi #powerbitutorial #dataanalytics
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/mastering-snowfl...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - [e-mail]
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!
#SnowflakeSchema #DAXCalculations #PowerBIDataAnalysis #SnowflakeSchemaTutorial #PowerBITips #PowerBIAdvanced

[@GoodlyChandeep]

Check out the Related function in DAX video ▶ - kzbin.info/www/bejne/bHPHn4SJYs2NrNksi=owDSoBp8N42e28nX Download the file ⬇ - goodly.co.in/mastering-snowflake-schema-calculations-power-bi

[@tak0331]

Hi Chandeep, at the 4:00 timestamp, you filtered to Jul-11 when you meant to select Jun. Not a huge deal as I can see Jul-11 also had an error in SUM from the original DAX formula. Great explanation & solution. Thanks!

[@datayoda74]

Excellent work 😊

[@mdandekar]

Was wondering the same…!

[@juja2819]

Was wondering about the same, but I think the explanation is to find at the 6:45 timestamp. It doesn't matter wich month is filtered at 4:00!?

[@tak0331]

@@juja2819 Agreed

[@GoodlyChandeep]

I was too engrossed to mis-select the filter but luckily it worked 🤣🤣

[@aartdevletter5822]

My man… U have no idea how often I am going to use this. Not just to really USE it, but now a lot of other stuff also suddenly makes sense… ❤

[@learnexcelwithfun4716]

Brother, I appreciate the straightforward explanation. You're truly invaluable.

[@TOV-zc9bp]

Well explained, straightforward and to the point. Thanks!

[@mishymoo34]

Looking for an excuse to use this idea in the future! Thanks Chandeep.

[@RyanGaroogian]

Great video very clear and smart to create secondary relationships that are not active and then call them specifically on specific DAX queries.

[@taylorcharlesmichel]

Very informative! Keep up the good content!

[@chrism9037]

Excellent, thanks Chandeep!

[@Jahspecs1]

Excellent video! Thanks!

[@RogerStocker]

3:53 You choose July instead of June in the refund table.

[@daxteoh27]

Love the explanation!

[@mathew9665]

Great comunication here - This is fundermental chaps - you need to ensure you use this process - even if there are multipule dates needing reporting the same fact table that need reporting

[@Nalaka-Wanniarachchi]

Nicely explained. love it

[@juja2819]

Awesome, thank you! :)

[@arbazahmad7177]

Excellent..🎉

[@gennarocimmino]

a big Thamks for the sharing of tutorial

[@sanumpzha]

Nice one Chandeep.

[@fog2640]

Finished watching

[@faxfax13]

Awesome 😆

[@shoaibrehman9988]

Superb

[@alexrosen8762]

Great use case 👌 More tutorials like this please

[@dilipinamdarpatil6301]

Thank you 🙏

[@RawShooters]

while checking the refund there was a little confusion July instead of June?

[@soumyendupaul9556]

As per my understanding Snowflake schema contains only Normalized Dimension table but I got confused to know that even it may contain normalized fact table also. Could you share any document on this?

[@raf9840]

Nice vid, question: wouldnt change it to both directions filtering in the relationship also work in this case?

[@royegd5679]

wow.....

[@vdj6298]

Thank you for your videos. 2 Questions: 1. May I ask why you didn't create a transaction ID table in Power Query, then used it along with your your date table to separately connect to the Sales & Refund tables, using the new transaction ID field in the new table in your visuals? 2. Is there a benefit to creating your calendar table in DAX as opposed to Power Query?

[@GoodlyChandeep]

Even if you do point 1, it won't help take the filters from the products or sales to refunds (in a one to many relationship format). Plus it makes the model unnecessarily complicated. I mostly create date table in power query but I used DAX for this one :)

[@andrescorredor7517]

Nice video and explanation. is there any performance degradation using this strategy?

[@GoodlyChandeep]

Not that I have faced

[@sajjad5189]

Hi, @8:55 the scope is limited to this measure only and not 'whenever we use calendar date in filter context' of any visual. Just a suggestion to be more specific.

[@SamanthaLAI-ok6vd]

This problem happens also when you merge 2 tables in power query as well

[@bulbulahmed3098]

❤❤❤

[@hussainmeghani4809]

Sir...pls help Sir, while click on content... Detail displayed but file name column remove.... How to fix the problem... I am extract csv format file through power query

[@findthetruth3021]

Hi, I have a very difficult question and I searched through the Google and chatgpt and everywhere but couldn't find a solution for that. I have an idea to combine two different maps one is going to be in shape file and the other one is going to be point map. So my idea is to make the background of the point map transparent and only leave the points by latitude and longitude so that I can make a group of points by latitude and longitud with the shaple file map and putting the points by latitude and longitud over the shape file. Finally, make them on group which will appear like that I have both shaple file plus points by latitude and longitud together. But until now I couldn't find a solution how to remove or make the point map background transparent or without having any colour except the points by latitude and longitud. Please help me in this regard. Thanks in advance.

[@abdelkaioumbouaicha]

📝 Summary of Key Points: 📌 Snowflake schemas are a type of relationship called a fact of a fact relationship, which presents challenges when creating calculations in Power BI. 🧐 The speaker's goal is to calculate the total number of units refunded, but they encounter a discrepancy in the results when filtering by month due to transactions being sold in one month but refunded in another. 🚀 The speaker suggests two approaches to solve this problem: deleting the relationship between the refunds table and the sales table and creating a new relationship directly between the refund date and the calendar date, or creating an inactive relationship between the refund date and the calendar date and modifying the calculation using the CALCULATE function and the secondary relationship. 💡 Additional Insights and Observations: 💬 "The relationships between the tables propagate filter context, which can lead to discrepancies in calculations when dealing with snowflake schemas." 📊 No specific data or statistics were mentioned in the video. 🌐 The speaker mentions their DAX, M language, and Power Query courses for those interested in learning more about Power BI. 📣 Concluding Remarks: This video provides insights into dealing with snowflake schemas and fact of a fact relationships in Power BI. It highlights the challenges of calculating values between tables with different granularities and demonstrates how to validate and fix the calculations. The speaker also mentions their courses for those interested in further learning. Generated using Talkbud (Browser Extension)

[@Sumanth1601]

3:58, you selected july instead of june

[@RawShooters]

or June Sale refund in July?

[@cjs313313]

The underlying data model is BAD. You don’t join ‘facts to facts’. The refund fact table should look just like the sales table. If you have one record per item sold in the Sales fact, you should have one item per refund in the Refund fact table. That is, they should be at the same grain. Some might argue that both your sales and refunds should (and can) be in the same table. Your refund table makes no sense. Jumping through all of this hoops in Power BI creates technical debt. Difficult to understand and maintain. You’re doing your viewers a disservice by demonstrating this. Fix it in the data model.

[@NX7one]

I suppose in the real word the Sales table would have one record per item which would allow the 2 facts to be merged. You don't always choose the data you work with though, you would probably design the same data model if you had to use the tables as they are presented in the video. 'You don't join facts to facts' - I see this statement quite often but I don't fully understand it, could you explain how would you model a many-to-many Order-Invoice relationship without joining the facts together via a bridge table?

[@Wzxxx]

Sry, this is total bullshit theory based on theories from MS and other sites or books, sometimes u have to join them. Theory and reality is way different, based on years of experience. Data and business reality doesn’t match theories. Connecting with same dimmensions, denormalizing etc…yeah and at the end u have to write ton la of code to kill cartessians, crossjoins and all this terrible stuff.

[@Irfankhan_56]

Your courses are too expensive please think about students or working professional who are from India also 🙏

[@XeniaD]

you said June and you select July kzbin.info/www/bejne/jWqUZpR7pqqesJY LOL!