A Fine Slice Of SQL Server
Devin Knight continues his Power BI custom visuals series:
In this module you will learn how to use the Drilldown Player Custom Visual. The Drilldown Player acts like a slicer that animates other visuals around it on your report.
Click through for the video. This can be a useful visual for kiosks or demonstrations, as it loops through the different categories.
Comments closedRob Collie is rabble-rousing and building up a pitchfork mob:
I don’t know how we’ve managed to go this long without the ability to Filter in the Data View:
As simple as it sounds, this is the #1 thing I miss when I leave the Excel Power Pivot environment for Power BI Desktop.
Why do I miss it? Debugging and Validating, primarily. When I’m getting results from a measure that I don’t trust, it is super common for me to filter a table in Power Pivot so that I’m just looking at a specific set of rows. And then I often filter by a second column, and then sometimes a third, in order to see if there are any unexpected surprises in the data. Scrolling through thousands of rows looking with my eyes just doesn’t cut it.
This is positively crucial, at least for me, and creating a Table or Matrix visualization to perform the same task is such a high-friction alternative that I’m usually tempted to start over in Power Pivot. Ugh.
In fact this is the overwhelming #1 reason why, if I’m starting a model from scratch, I try to do as much of the work as I can in Power Pivot before converting over to PBIX format.
Rob has provided a link to the Power BI Ideas forum, making it easy to vote this item up.
Comments closedLeila Etaati shows how to use Plotly to generate interactive R charts in Power BI:
In the last two posts (Part 1 and 2), I have explained the main process of creating the R custom Visual Packages in Power BI. there are some parts that still need improvement which I will do in next posts. In this post, I am going to show different R charts that can be used in power BI and when we should used them for what type of data, these are Facet jitter chart, Pie chart, Polar Scatter Chart, Multiple Box Plot, and Column Width Chart. I follow the same process I did in Post 1 and Post 2. I just change the R scripts and will explain how to use these graphs
Leila includes several examples of chart types and shows that it’s pretty easy to get this working.
Comments closedDevin Knight has a new entry in his Power BI custom visuals series:
In this module you will learn how to use the Drilldown Choropleth Custom Visual. The Drilldown Choropleth is a map visual that displays divided regions that are highlighted indicating the relative value in each location.
Click through for Devin’s video and example.
Comments closedMatt Allington explains what the ALL() function is in DAX and when you might want to use it:
The ALL() function seems very simple on the surface however it has layers of complexity. In its most simple usage it is a function that simply returns a table (virtual or materialised). The syntax for ALL() is as follows
=ALL(TableOrColumn,[Column2],[ColumnN]..)
ALL() will always return a table, not a value. Because it is a table, you cannot put the result directly into a cell in a Pivot Table or a Matrix. Think about it, you can’t put a table with (potentially) multiple columns and (potentially) multiple rows into a single cell in a visual – it wont “fit”.
There’s a lot to ALL() and Matt does a great job explaining it.
Comments closedLeila Etaati continues her series on using neural nets in Power BI:
we are going to predict the concrete strength using neural network. neural network can be used for predict a value or class, or it can be used for predicting multiple items. In this example, we are going to predict a value, that is concrete strength.
I have loaded the data in power bi first, and in “Query Editor” I am going to write some R codes. First we need to do some data transformations. As you can see in the below picture number 2,3 and 4,data is not in a same scale, we need to do some data normalization before applying any machine learning. I am going to write a code for that (Already explained the normalization in post KNN). So to write some R codes, I just click on the R transformation component (number 5).
There’s a lot going on in this demo; check it out.
Comments closedMatt Allington explains how to use the Combine feature in Power Query:
Now the good news is that somehow the combine button has done a reasonable job at combining the files (see 1 below). Actually I wanted to unpivot the month columns, but I will come back to that later.
The bad news is all of the activity over on the left hand side. What the…? There are 5 additional “Queries” on the left (numbered 2 – 6) that do all sorts of things. Let me tell you what each of these are and then come back and explain how to use/interpret these things.
2. This is a parameter that can be used to change the sample file
3. This is the link to the sample file that was selected originally (I selected the first file in the folder and this is the link to that file).
4. This is the “by example query” – the most important query to know about.
5. This is an auto generated function that goes with 4 above.
6. This is the final output query (it is the query that is displayed in 1 above).
Matt clearly explains the whole process, so read on if you need to combine files of Power Query.
Comments closedChris Webb explains when you might get a Formula.Firewall error in Power BI or Power Query:
The important difference here is that there is now one step in this query instead of two: the query and the filtering take place in the same step. Even more importantly, regardless of the data privacy settings, the query fails with the error:
Formula.Firewall: Query ‘DimDate With Native Query Single Step Fails’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
The problem here is that the Power Query engine is not allowed to access two different data sources originating from different queries in the same step – as far as I understand it this is because it makes it too hard for the engine to work out whether a step connects to a data source or not, and so which data privacy rules should be applied.
This is an interesting downside to putting in complex data privacy rules.
Comments closedI’m going to share in this blog a technique I’ve found useful in Power BI for collecting measures together in once place AND placing them at the top of the field list.
The good news is, calculated measures do not have to exist on the table that stores the underlying data specific to that measure. Measures can be placed on any table in the model and they will still work as expected. This may not be immediately obvious but it’s handy to know.
So far, I’ve kept measures on their logical best-fit tables, but Philip’s hint looks quite useful once the set of measures grows, or if there are a number of cross-table measures.
Comments closedDavid Smith reports on a great update to Power BI:
The above chart was created with the plotly package, but you can also use htmlwidgets or any other R package that creates interactive graphics. The only restriction is that the output must be HTML, which can then be embedded into the Power BI dashboard or report. You can also publish reports including these interactive charts to the online Power BI service to share with others. (In this case though, you’re restricted to those R packages supported in Power BI online.)
Power BI now provides four custom interactive R charts, available as add-ins:
Time-series forecasting using the forecast package, with interactive range selection, data selection and tooltips (code)
Time-series forecasting using the arima package, with data selection and tooltips (code)
K-means clustering, with data selection and tooltips (code)
Spline smoothing charts, with data selection and tooltips (code)
I’d avoided doing too much with R visuals in Power BI because the output was so discordant—Power BI dashboards are often lively things, but the R visual would just sit there, limp and lifeless. I’m glad to see that this has changed.
Comments closed