My Journey with Power BI

For Power BI data modeling, star schema is recommended by gurus like Marco Russo, Alberto Ferrari all the time. The other day, I have understood the importance of using star schema one more time. You know what they say: Experience is the best teacher. Better than Marco Russo and Alberto Ferrari. Definitely. This mode l looks more like Dalton Brothers than snowflake schema but it's called snowflake schema. Not to be confusing, I removed other dimensions which are not relevant to our subject. As you see there are one to many relationship between all of them and the filtering direction goes from high granularity to low granularity like it's supposed to. In the first table, the Sub-Category column coming from the Products Sub Category table and the Total Sales measure is the sum of the sales column. With All Sub-Category measure, I remove the filter on the Products Sub Category table by using the ALL function. No problem so far. The problem appears in the second table. My All Sub-C...

I've found a bug the other day by chance. When I searched about it, I've found out that it's an unsolved issue for years. If there's an issue that hasn't been solved for years, it definitely deserves a post, even a name. In my opinion, bugs should be named after their discoverers.  So here I am, with my bug.  Before I get to the point, I'd like to clarify what the HASONEVALUE function does. Microsoft says: 'Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.' This function is not very intuitive because of its confusing name. Think it more like hasonefilter. It returns TRUE if the column is filtered by only one value.  The measure called time you see below calculates this: If nothing is selected, print "Choose Date" if zero is selected print "Today", if null is selected print "Blank", otherwise extract the selected number as the number of days from today's da...

This is the last post of my join series and it's about how to create a Cartesian product table in DAX and in Power Query. If you are wondering what is Cartesian product, google it :) I'm joking come back here :) Okay, serious mode on. First I'd like to share Wikipedia's definition and visual to describe it: ' In mathematics, specifically set theory, the Cartesian product of two sets A and B, denoted A × B, is the set of all ordered pairs (a, b) where a is in A and b is in B. In terms of set-builder notation, that is:' My definition for Cartesian product (in Power BI) is taking two different tables and combining them with all possible pairs of rows. To get this result in DAX we can use CROSSJOIN function. Microsoft's definition for CROSSJOIN is: "Returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables." The syntax is pretty simple:  ...

It's always said that we should prefer stored procedures in SQL other than ad hoc queries. But why should we do that? In this post, I'm going to focus on the reasons for that.  Have you ever wondered what happens in the background when we query 'Select * From Table'? I bet you didn't. Then, let me explain what happens. In SQL, every ad-hoc query is processed through 6 different steps. And these are the steps:  The first step is the query. In this step, we send our query to SQL by using a tool. This tool can be SSMS, Excel, etc... The second one is called Parse. During Parse, SQL checks if our query is syntax error-free or not. The other thing that it does is access control. It checks if we have permission to access the table. Optimize is the part where SQL decides which index to use, by checking the statistics of indexes. When we click on 'Display Estimated Execution Plan' we can see which index SQL choose. During Optimize, SQL always chooses the index which...