A Fine Slice Of SQL Server
In my recent open data project, I created a single page report model with a sparse slicer. It’s a good trick for anyone who wants to make their slicer look a bit sleeker. Like any other visual in Power BI, Slicers also have many properties. By default, below is how slicer looks in Power BI, but I made few changes to make it look like the one on left, in a few steps.
Click through for the video.
Comments closedPaul Turley continues a discussion on Power BI data model validation:
We often have users of a business intelligence solution tell us that they have found a discrepancy between the numbers in a Power BI report and a report produced by their line-of-business (LOB) system, which they believe to be the correct information.
Using the LOB reports as a data source for Power BI is usually not ideal because at best, we would only reproduce the same results in a different report. We typically connect to raw data sources and transform that detail data, along with other data sources with historical information to analyze trends, comparisons and ratios to produce more insightful reports.
However, if the LOB reports are really the north star for data validation, these can provide an effective means to certify that a BI semantic model and analytic reports are correct and reliable.
Click through for more details.
Comments closedJoseph Yeates looks at some of the new additions to Power BI:
It seems like I cover updated functionality to the Q&A feature every month now! This month, it is the ability to do arithmetic in the visual!
Below, I added together two recognized terms to have the sum displayed. I didn’t need to create a DAX measure to achieve this, it is all done within the visual.
Click through for more.
Comments closedI know that some of this might be able to be done with other calculation groups. I find I have more flexibility when combining Calculation Groups with a disconnected table.
Below are some of my previous calculation group blog posts that might also be of interest:
Click through for the demo.
Comments closedMartin Schoombee gets off the beaten path:
We’ve only used the PowerShell cmdlets for Power BI so far in this series, but things are about to get interesting because there aren’t cmdlets available for everything you might want to do. One such thing is updating parameters, and we’re going to use the Power BI REST API (which the cmdlets use underneath the covers anyways) to achieve that.
REST APIs are usually a little tricky to deal with, especially the process of authentication. Fortunately there is an Invoke-PowerBIRestMethod cmdlet that makes it possible to use the API in PowerShell without the need to deal with some of the underlying complexities.
Read on to see how the whole process works.
Comments closedIf you’ve used Power Query long enough, you’ve needed to group data and provide some sort of summary. For example, grouping by customer and summarizing sales. The normal way the Table.Group function works is to group the entire table by the field(s) you are grouping by, then providing whatever aggregations you need.
However, you can alter that behavior and group data by a column, and have it provide a separate group at each change in the data.
Click through for the rest of the story. There’s some complexity to the problem which is hard to summarize.
Comments closedMarco Russo and Alberto Ferrari walk us through three important set functions in DAX:
In this article we refer to “set functions” as functions that operate on sets. The three set functions available in DAX are: UNION, INTERSECT, and EXCEPT. Their behavior is very intuitive:
– UNION performs the union of two or more tables.
– INTERSECT performs the set intersection between two tables.
– EXCEPT removes the rows of the second argument from the first one.These functions take two or more tables as parameters and return a table. They prove useful not only to write DAX queries; a developer can also use these functions to prepare complex filters when implementing measures.
Read on to see how these work in DAX.
Comments closedShabnam Watson has a helpful script for us:
You can use PowerShell to download all of your PBI reports in a workspace all at once without having to go through the PBI service UI one at a time. As an added bonus, you may notice that downloading a report with PowerShell is faster that downloading it through the PBI Service UI.
This script is useful for admins to take backups of reports deployed to PBI Service. It can be easily extended to loop through all/several workspaces. It is also useful for developers to take a backup of their report before publishing a new version.
Click through for the script.
2 CommentsChris Webb wants to track query concurrency when loading data into Power BI:
Most of the time I’ve spent looking at Power Query Query Diagnostics data, I’ve been looking at data for a single query. Over the past few days though I’ve spent some time investigating what this data shows for all the queries that are executed for a single dataset refresh. To help me do this I wrote the following M function:
Click through for the function, as well as ways of visualizing the results.
Comments closedGilbert Quevauvilliers has an interesting solution to a common problem:
I had a requirement where the client wanted the capability to decide how much data to load from a SQL Server Query (TSQL). This was so that they could limit the dataset returned, as at times they did not need all the data.
So below I demonstrate how to achieve this.
NOTE: This will be slightly advanced because I had to manually add some code in the Advanced Editor in Power Query.
Maybe it’s because of the number of times I had to do this with Reporting Services, but this seems like it should be a lot easier than it is.
Comments closed