A Fine Slice Of SQL Server
Dataset Parameters are a way to bring some sort of dynamic into your Power Query datamodelling in Power BI. For my blog post about Modifying Parameter values in powerbi.com I was looking for a way to display the value of a parameter inside a Power BI report.
In this blog post I would like to show you the steps that are required to bring your parameters to your field list – and as a consequence – into your Power BI report.
Read on to see how to do this.
Comments closedCedric Charlier shows how to perform time zone conversions with the M language in Power Query:
Everything is fine … except if I share my code with someone from another time zone. The function
DateTimeZone.ToLocalis relying on regional settings and in that case my conversion should always be from UTC to “Brussels time”.I didn’t find any other way to ensure that I’m always converting from UTC to “Brussels time” than implementing the conversion by myself. That’s the goal of the following function
Looks like there may not be a nice “convert to a different time zone” here like lubridate::with_tz() does in R.
My account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.
Steve shows us where you can go to disable this if you want, as well as places where you can see what content has been shared.
Comments closedEugene Meidinger hunts down where those local Power BI credentials live:
With SSIS, you have to be careful to export the SSIS files without any sensitive information included. But what about Power BI? If you save the .PBIX files on OneDrive, can you be exposing yourself to a security risk?
Looking at things, it looks like credentials for data sources are stored globally, so one wouldn’t expect them to be in the .pbix files.
Read on as he does some more sleuthing and discovers the answer.
Comments closedChris Webb has an interesting use case for adding primary keys on lookup tables:
As you can see, the Property Type column from the #”Price Paid” query contains single letter codes describing the type of property sold in each transaction; the Property Type column from #“Property Types” contains a distinct list of the same codes and acts as a dimension table. Again there’s nothing interesting going on in this query.
The problems start when you try to join data from these two queries using a Merge and then, for each row in #”Property Types”, show the sum of the Price Paid column from #”Price Paid”.
Although baseline performance is bad, Chris shows a way of improving that performance significantly.
Comments closedStacia Varga digs into her hockey data set a bit more:
Once I know whether a variable is numerical or categorical, I can compute statistics appropriately. I’ll be delving into additional types of statistics later, but the very first, simplest statistics that I want to review are:
- Counts for a categorical variable
- Minimum and maximum values in addition to mean and median for a numerical value
To handle my initial analysis of the categorical variables, I can add new measures to the modelto compute the count using a DAX formula like this, since each row in the games table is unique:
Game Count = countrows(games)
It’s interesting seeing Stacia use Power BI for exploratory analysis. My personal preference would definitely be to dump the data into R, but there’s more than one way to analyze a data set.
Comments closedThat said, there is one feature from my previous blog that was not implemented in Power BI – highlighting scatter/bubble charts. In Power BI, the scatter charts are not considered as area charts and hence you can only filter them and not highlight. This feature is useful when you have a lot of data points in your scatter chart and you want to see where a particular data point is with respect to the other data points. That said, you can make use of some nifty DAX and replicate the same behavior.
There are several steps to the process so it’s not point-and-click easy, but Jason has a nice walkthrough showing how to set it up.
Comments closedJason Thomas has put together a great Power BI report:
I thought it might be useful for some enterprise customers to see what the total cost is going to be for 3 years, and decided to share it here. You can use this guide to see some of the additional information like:-
This is rather useful for long-term planning.
Comments closedMeagan Longoria has some tips to make your Power BI reports easier for people to read:
Avoid using color as the only means of conveying information. Add text cues where possible. It’s very common to show KPIs with a background color or a box next to a metric that uses red/yellow/green to indicate status. Users who have difficulties seeing color need another way to understand the status of a key metric. This could mean that you use a text icon in addition to or instead of color to indicate a status. Power BI reports often include conditional formatting to change the background color or font color of items in a table to convey high/low or acceptable/unacceptable values. If that is important for your users to understand, you could add a field containing the values “high” and “low” to the table itself or to the tooltips. Tooltips are accessible to screen readers via the accessible Show Data table (Alt + Shift + F11).
These are good design principles in addition to providing accessibility benefits.
Comments closedIke Ellis has a quick tip for managing multiple Power BI accounts across different clients:
As a consultant, I find it difficult to switch between accounts on PowerBI.com.
I have to log out of an existing account and log back in to a new account. The login process takes a long time. I have found a work around. I use google chrome to manage different chrome accounts, different themes, different cookies, and this allows me to stay logged in to multiple power bi accounts at the same time.
Great tip.
Comments closed