My Journey with Power BI

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