My Journey with Power BI

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

First of all, why do we need to change the language of the calendar? If we're working on a report, which is going to have multiple languages for different users, changing the language of the calendar is a must and there are 2 ways to do that. The first one is the one that I don't recommend, changing the current file's regional settings from options and settings menu. This is the option to change the file's language and it's quite handy when we need to do that. But what if we only want to change the calendar's language, not the whole file. And what if we want to have multiple language options for our calendar. In that situation,  this menu is not going to be much of a help. So how can we do it? First, let's look at our date table code in M language. As you see, in lines 12 and 14. Date.ToText function is used to create Monthname and Dayname columns. Date.ToText function's name is quite self-explanatory. It converts date numbers to text. If you'd like ...

Advanced Elements is not an intuitive description, that's why I'm going to show you what I'm talking about directly. For my recent project, I've decided to create my own theme for the report. When I was exploring Customize Theme tab, I have come up with the Advanced section. While text, visuals, page, and filter pane sections are self-explanatory, this advanced section seemed quite enigmatic to me. What are the first-level elements? Or the others? I think Microsoft agrees with me upon being enigmatic because the section comes with "Learn more" option: Click to  Learn More . But when I tried to learn more, it didn't make any sense to me, because I'm a visual person and those descriptions are quite hard to remember. That's why I have decided to be a good samaritan and visualize it, and then share it with you.  As you see, while the border is a third-level element, the shadow is a first-level element. Legend, x-axis, and y-axis are second-level elemen...

Before I get to the point, I'd like to give short information about transpose and unpivot options at Power Query Editor, in case you don't know. Transpose option converts rows to columns and columns to rows in the given table. On the other hand, the unpivot option converts horizontally stored data into a vertical format. After unpivoting, you get two new columns. They are called attribute and value. The attribute column consists of data of header of columns and the value column consists of whatever you have under header columns. But, to be able to transpose or unpivot successfully, there is one thing you should be aware of. And this post is about that one thing. In this table, I have countries and their GDP per capita values starting from 2000 until 2020. This table format is not ideal for analyzing the data because it has too many columns that actually should be rows. That's why I want to convert rows to columns and columns to rows by transposing. After I click on the tran...

If you haven't come up with the wrong totals in Power BI yet, you're either lucky or you have come up with them but nobody has noticed it including you, and in that case, you're still lucky.  I have created a What If scenario to see how my sales would change, if my sales rise up to some specific percentage, for example, %100. If you don't know what is that or how to make scenario analysis you can watch DAX Fridays! #53: GENERATESERIES . Anyway, the first measure "Sales" shows the sum of sales. The second one shows the sales if, the sales rise up %100. And the measure of that is:  Sales Scenario Value measure brings the selected percentage: So far, everything works just fine. Here comes the measure for the third column: In the third column, I want to apply a %100 sales increment scenario only for one product, called Canon Ink, Color, and see how that affects my total sales. For Canon Ink, Color, I apply the %100 increments on the sales scenario, and for the oth...

My last post was about the CROSSFILTER function and I have decided to add more combinations to it by using the USERELATIONSHIP function. If you don't know the CROSSFILTER function, you can check my last post  Why And How To Use CROSSFILTER . Let's start with an example: On this modal, the relationship between Facts table and Orders table is inactive. I didn't make that relationship inactive, Power BI itself made it automatically. And why did it do it? Let's say I want to count no of Order ID by date. Order ID column is in Orders table, whereas Date column is in Calendar table. To be able to calculate that, I need to propagate the filter from Calendar table to Order table. And I have two paths to propagate that filter. The first one is directly from Calendar to Order, the second is from Calendar to Facts and then to Order table. So which path should it take? This creates ambiguity for Power BI and to solve that ambiguity, Power BI inactivates the second one automatically...

A relationship between two tables can be unidirectional or bidirectional. When it is unidirectional it filters from one side to many side, not the other way around. If we need to filter from both sides, we can make the relationship bidirectional but it might come with a heavy cost.  Having too many bidirectional cross-filter in a data model creates ambiguity between relationships and it might give us wrong results in the end. If this sounds Chinese to you, look at the modal below:  On this modal there two paths to reach from Calendar Table to Location Table. When there are multiple paths between tables, Power BI might find the right way to propagate filter or it can't, thus give us wrong results with our calculations without even complaining about relationships. That's why bidirectional cross-filter relationships can be dangerous and we should stay away from them. But how? What if we need to filter from both tables which have unidirectional filters. In this situation, we can a...