My Journey with Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...

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:  ...

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about. The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print  "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: ...

This post is about how to perform left join and right join in DAX, and in Power Query. If you need to refresh your memory about join types, you can read my post about it  here .  NATURALLEFTOUTERJOIN is used to perform left join in DAX. Left join means combining two tables by taking all the rows from left table and only the matching ones from right table. Microsoft's definition for this function is exactly the same as their definition for NATURALINNERJOIN meaning somebody made a wrong copy-paste. Here comes their links: https://docs.microsoft.com/en-us/dax/naturalleftouterjoin-function-dax https://docs.microsoft.com/en-us/dax/naturalinnerjoin-function-dax The syntax is pretty simple: NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>) Like I always do, I am going to explain it over an example. These are my tables that I'm planning the join.  This is the formula to join them and the error message comes with it. Like any other join function in DAX, it requir...

This post is about how to perform inner join in DAX. If you don't know what that is you, can access my previous post  here  which is about join types. Microsoft's definition for NATURALINNERJOIN is: " Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned." And the syntax is: NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)   First of all the definition is wrong. I had two tables with no common column names (check the image below) and I got no error. This definition is really misleading because it makes you think that Power BI creates connection between columns by using common column names but that's not what happens. You need to have a relationship between 2 tables to perform inner join. This Microsoft definition definitely needs correction. These are my tables and below is the formula with the result table. As you...

Power is like the year 2020, full of surprises. You must be wondering what did it do again! Well, it really surprised me the other day. When I was exporting data from Power BI, I've faced a very unexpected result, and this post is about that result. And of course, about the solution to that. This is my data table. As you see, the table has 23,443 rows. On the report view, I have created a table visual by taking all the 23,443 rows and all the columns of this table. And there is no explicit filter on that visual like report level, page level, or visual level filter. After that, I have clicked on Export data option. That option creates a CSV file that you can save on your computer. And this is the CSV file that Power BI has created. In the beginning, everything seemed quite normal. But when I kept scrolling down, I have noticed that I have much less row than I was supposed to have. When I was creating this file I was hoping to find all the 23,443 rows on the visual. But 167 is not ev...

In February 2017 Power BI introduced a new function called TREATAS. It simply creates virtual relationships between tables. But why we should create virtual relationships instead of physical ones? Actually, we shouldn't. It is recommended to use physical relationships whenever it's possible, but Power BI is not always sunshine and rainbows. Especially when we need to create relationships between tables that have different granularities. For this example, I have an advertising budget table for the year 2019 and a calendar table for many years: In this situation, I need to create a relationship between my budget table and calendar table, so that I can create time intelligence measures. But I can't do it, because these two tables have different levels of granularities. While the calendar has a row for every day, the budget table has a row for every month, which means my calendar has higher granularity than my budget table. If we create relationships between tables that have di...