Category: Power BI

Reza Rad has a new version of Power BI Helper out:

It is a pleasure to announce the newest version of Power BI helper, version 3.0 July 2018 with the great feature of exporting model documentation. The documentation part of the insight from Power BI Helper has been always in our backlog, but haven’t had a chance to work on it. Gladly now you can export the document to an HTML file. The exported documentation at the moment, has information about all tables in the model, all measures, all columns and calculated columns in each table with possible expressions and descriptions. If you like to learn more about Power BI Helper, read this page.

Read on to see what Power BI Helper has in terms of documentation.

Teo Lachev shows us the importance of defining relationships in Power BI:

However, If there isn’t a direct relationship between ResellerSales and Employee, the moment you add an unsummarized field from the second table on the many side, such as Employee[FullName] after adding SalesTerritoryCountry and ResellerSales[SalesOrderNumber), you’ll get the error “Error: Can’t determine relationships between the fields”.

Solution: Interestingly, the report works fine if a summarized field, such as COUNT(Employee[EmployeeKey]) is used. In this case, the SalesTerritory dimension acts as a conformed dimension joined to two fact tables. The reason why it doesn’t work when Employee[FullName] is added is because there is no aggregation on the Employee table and the relationship between ResellerSales[SalesOrderNumber] and Employee[FullName] becomes Many:Many over SalesTerritory which is now a bridge table. One employee may be associated with multiple sales and a sale can be associated with multiple employees. How do we solve this horrible problem?

Good data modeling is important, and Power BI dashboards are no exception to the rule.

Matt Allington walks us through a somewhat complicated scenario with income stream projections but inconsistent monthly payout rates:

The first thing I decided to do was work out which was the first month of the policy.  I used LOOKUPVALUE to do this because the Calendar table was not connected to the policy table.  Instead of returning the actual month name, I returned the unique MonthID from my calendar table.  I ALWAYS load a unique MonthID in my calendar tables as they are REALLY useful. In short this column is an integer, starting at 1 and incrementing by 1 for each month in the table.  So starting in Jan, after 3 full years the MonthID will be 36.

Pol First Month = LOOKUPVALUE (
     'Calendar'[Month Index],
     'Calendar'[Date],
     MAX ( Policies[Pol Date] )
)

After writing this measure (and all subsequent measures), I placed the measure in the pivot table shown below.  This pivot table has the policy date from the policy table on rows and the YYMM from the Calendar table on columns.  I did this for a very important reason. I knew this would be the way I needed to see the final results and therefore I needed to make sure all measures returned the expected outcome once this layout was used.  And don’t think I wrote the all the formulas correctly to achieve this outcome the first time.  In fact I made plenty of mistakes.  But the point is I wouldn’t have known they were mistakes if I didn’t place the result in a pivot table like the one below.

It’s a good example of breaking apart a fairly complicated concept and rethinking equations as set operations in DAX.

Matt Allington needs a haircut:

I got my haircut today (pretty spiffy one too, even if I do say so myself).  While I was chatting I asked my hair dresser “on average, how often should I get my hair cut”?  She told me (for men) around 4-6 weeks.  Then I got thinking (as I do), I wonder if I could data-mine my credit card data using Power BI and find out how often I actually get my own hair cut.  It turns out I was able to do this, and this article explains the hardest part of that task – find the number of days between two transaction dates using DAX.

I’d probably end up doing this in SQL with the LAG function, but it’s good to know several ways to solve date difference problems.

Imke Feldmann shows how to flatten JSON data imported into Power BI:

If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what’s in there. The following function has you covered for this task. It returns a table with all values in column “Value” and additional columns describing where that value came from in a hierarchical form, no matter how deep the nesting goes:

Click through for a script which shows that the process is a lot more complicated than I had expected.

Chris Webb shows an easy-to-miss feature in Power BI:

The other day I discovered something new (at least to me) while writing the DAX for a measure in Power BI Desktop: when you insert a new line in your DAX expression using SHIFT-ENTER it also auto-indents the code. I asked a few people if this was new because I was sure I hadn’t seen it before, even though I always put line breaks in my code; of course Marco had and said he thought it had been around for a while. Anyway, Marco then commented that most people didn’t know you could even put line breaks in DAX and I thought to myself I should probably write a short blog post about all this, because of course line breaks and indentation make your code much more readable.

Click through for a demo as well as a couple of tips around this.