Kasper de Jonge shows us different ways of sorting a visual by some unrelated measure:
So lets start with the simple one, I want to sort a chart on a measure not part of the visual. Let’s take this visual:
Now instead of sorting by OrderQuantity I want to sort by the ListPrice. The trick here is to make the measure part of the query, and one way you can do that is by adding it to the tooltip
Read on for examples for charts as well as matrices.
Tools like Power BI have changed reporting allowing power users to leverage tabular cubes to present information quicker and without the (perceived) need for developers. However, experience tells us many users still want data in tables with a myriad of formatting and display rules. Power BI is not quite there yet in terms of providing all this functionality in the same way that SSRS is. For me, SSRS’s great value and, at the same time its curse, is the sheer amount of customisation a developer can do. I have found that almost anything a business user demands in terms of formatting and display is possible.
But you have invested your time and money in a tabular SSAS model which plays nicely with Power BI but your users want SSRS reports so how to get to your data – using DAX, of course. Using EVALUATE, SUMMARIZECOLUMNS and SELECTCOLUMNS you can return data from a tabular model in a tabular format ready to be read as a dataset in SSRS.
It’s a good post and a good example. The only quibble I have is in the motivating paragraph; Power BI and SQL Server Reporting Services have different end goals—Power BI isn’t (and I think never will be) a pixel-perfect report building product; it’s meant to be a dashboarding technology. That quibble aside, the example is well worth checking out.
Paul Turley offers up some guidance on when to use which language when building Power BI models:
As a general rule of thumb, in formal SSAS projects built on a relational data mart or data warehouse that is managed by the same project team as the BI data model, I typically recommend that every table in the model import data from a corresponding view or UDF stored and managed in the relational database. Keep in mind that is the way we’ve been designing Microsoft BI projects for several years. Performing simple tasks like renaming columns in the SSAS data model designer was slow and cumbersome. Performing this part of the data prep in T-SQL was much easier than in SSDT. With the recent advent of Power Query in SQL Server Data Tools, there is a good argument to be made for managing those transformations but the tool is still new and frankly I’m still testing the water. Again, keep changes in one place for future maintenance.
Do your absolute best to avoid writing complex SQL query logic that cannot be traced back to the sources. Complicated queries can become a black box – and a Pandora’s box if they aren’t documented, annotated and easy to decipher.
But do read Paul’s closing grafs on the importance of not being hidebound.
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.
Reza Rad has a new version of Power BI Helper:
I started Power BI Helper with the intention to help to find issues in Power BI reports faster and easier. This tool over time became better and better. I’m excited now to let you know that the version 2.0 of this product is now available for everyone to use and enjoy. This version comes with these features:
Connecting to more than one Power BI model. Selection option for the model.
Showing the connection mode of the Power BI file.
Showing list of tables that are NOT used in any visualization, and can be hidden from the report.
Modeling advises
- List of both directional relationships
- List of inactive relationships
Some minor bug fixes
It looks like quite the useful tool.
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.
Marco Russo shows us a way to optimize mutually exclusive conditional calculations using DAX:
In previous articles, we discussed the importance of variables and how to optimize IF functions to reduce multiple evaluations of the same expression or measure. However, there are scenarios where the calculations executed in different branches of the same expression seem impossible to optimize. For example, consider the following pattern:
Amount :=IF(<condition>,[Credit],[Debit])In cases like this involving measures A and B, there does not seem to be any possible optimizations. However, by considering the nature of the two measures A and B, they might be different evaluations of the same base measure in different filter contexts.
Read on for a couple of examples.
Melissa Coates gives us the different forms of what an “app” is in the Power BI world:
Let’s say you just heard someone mention a Power BI app. What exactly do they mean by that? Well, it depends. The term “app” is used kind of a lot in the Power BI world. So, here’s a quick reference to help you decode the conversation. I’m going to start with the most likely options, working down to other options. Which one someone is referring to really depends on their role and their level of familiarity with the Power BI ecosystem.
Power BI App
A Power BI App is a packaged up set of content in the web-based Power BI Service. Related reports, workbooks, dashboards, and datasets are published from an App Workspace into an App for users to consume.
Power BI App Workspace
An App Workspace in the Power BI Service is where reports, workbooks, dashboards, and datasets are saved, and where data refresh schedules and other settings are defined. An App Workspace is suited to development & collaboration with coworkers (whereas My Workspace is a private area). Smaller teams might do everything they need to do within an App Workspace, whereas larger teams use an App Workspace as the collaboration area for content before it gets published to a Power BI App for consumption. You can have quite a few App Workspaces, depending on how you organize content (for instance, by subject area, by project, by department, or by type of analysis).
Click through for several other potential answers for what that user means by “app.”
Phillip Seamark goes and creates a blackjack game in Power BI:
The last of the three data-tables in the model used to control Player 1 is the ‘P1 Turn’ data-table. This is simply a 10-row single column table with numbers 1 through 10. The purpose of this table is to use in conjunction with a slicer that will help keep track of what turn Player 1 is up to. A series of 5 bookmarks will be used to snapshot a slicer selected in 5 different states. A series of bookmarks will be taken with this slicer having a different value selected which is how the game can keep track of the progress through the game.
A [P1 Card Filter] calculated measure keeps track of the selected value over the above slicer which is used as a filter on the table-visual that is used to reveal cards for Player 1.
It’s more a toy solution than an actual game, but it’s interesting to see.
Kevin Feasel
2018-07-12
Power BI, Visualization
No Comment