Want to give a special thanks to the Microsoft PowerBI team for allowing PowerBI.Tips to participate in the 2018 Microsoft Business Application Summit. If you were not able to make it to this event don’t worry, we are bringing the PowerBI.Tips session to you.
The 2018 Top Tips and Tricks from PowerBI.Tips Session:
If you want to watch all the content from the Business Applications Summit you can zoom over to the official page here.
Be sure to follow:
If you like the content generated from PowerBI.Tips please follow me on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to me on YouTube. Or, follow me on the social channels, Twitter and LinkedIn, where I will post all the announcements for new tutorials and content.
I am proud to announce another joint development game between Philip Seamark and myself (Mike Carlo) of a Tic Tac Toe game. To read about how this game was made jump over to Phil’s blog post found here. If you just want to play the Tic Tact Toe game you can do so below in this Embedded sample found below:
If you like the content generated from PowerBI.Tips please follow me on all the social outlets. Stay up to date on all the latest features, free tutorials and downloads. Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn, where I will post all the announcements for new tutorials and content.
Sometimes when your working on a line chart you want the x-axis to stay centered on a chart. This tutorial will walk you through how to create an X-Axis that will always center it’s self on the graph.
Video Support Material:
The measures discussed within this tutorial are:
Variance All = SUM(Sales[Variance])
The column name Variance is found in the data table called sales. This is just a numerical column.
After summing up all the variances we can calculate the min and max lines.
Const Max Line = [Variance All] * 1.2
Const Min Line = -1 * [Const Max Line]
Finally to calculate the variance to date you can use this filtered measure, which will only produce historical values.
Variance To Date = CALCULATE([Variance All], FILTER('Sales','Sales'[Date] <= EOMONTH(TODAY(),0)))
Thanks for watching our short tutorial. If you like this video please be sure to follow me (Seth Bauer) on Twitter, LinkedIn and be sure to subscribe to the PowerBI.Tips YouTube channel.
This week Philip Seamark, an avid Power BI developer has released a joint project with PowerBI.Tips, a full Sudoku game in Power BI. To be totally honest with you Phil did all the hard work, I just contributed the pretty background and provided some suggestions. To learn more about how Phil build this amazing game within Power BI check out his blog post about it here.
The Game
If you want to play this Power BI file you can do so below:
If you are interested in looking at this file to see how it works you can download the file using the link below.
[product id=”18054″ ]
Be sure to follow:
If you like the content generated from PowerBI.Tips please follow me on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn where I will post all the announcements for new tutorials and content.
This weeks tutorial focuses on the need to control groups of visuals independently. This recently came up in a project where I needed to adjust all the items on the left side of the screen independently from the right side. By using the Edit Interactions button found on the Format ribbon you are able to adjust how different visuals interact with each other. Finally, adding multiple Slicers to the page for controls finishes out the report. I hope you enjoy this weeks tutorial.
Followup:
On the demo page of the report you’ll notice that when various items are selected, some of the non-selected items dis-appear. This is handled by using some formatting within the measures for the visuals. All the measures used in this tutorial are listed below:
Taking an Average of a Numerical Column:
Average of Values =
VAR calc = AVERAGE( Data[Value] )
RETURN if( calc = BLANK(), "", calc )
Making Dynamic Titles off of a list of items in a table:
Title =
VAR title = CONCATENATEX( VALUES( Data[Customer] ), Data[Customer], " & " )
RETURN if( title = BLANK(), "", title )
Producing a sum of values:
Total of Values =
VAR calc = SUM( Data[Value] )
RETURN if( calc = BLANK(), "", calc )
Want the file:
Need a little more help? Like the content from PowerBI.Tips. Please consider purchasing the demo PBIX file to support more great content.
[product id=”17894″ ]
Be sure to follow:
If you like the content generated from PowerBI.Tips please follow me on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn where I will post all the announcements for new tutorials and content.
Hands down best feature this year to date, Data Table Filtering! In the June 2018 Power BI Desktop Microsoft released the ability for you to navigate to the Data Table view. While on this view drop down icons now appear which enable filtering of the Data Table. This is super helpful when looking at the raw data that has been loaded into your data model. Check out the video below to see how the feature works.
Other Thoughts:
For those of you who like Excel, and data tables in Excel, this feature will make you feel right at home within a pivot table type feel. I hope you enjoy this month’s update as much as I did. Thanks for stopping by.
For the official documentation from Microsoft follow this link to the blog announcement.
If you like the content generated from PowerBI.Tips please follow me on all the social outlets to stay up to date on all the latest features and free tutorials.
Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn where I will post all the announcements for new tutorials and content.
Often when working with a Power BI report you will add a slicer that has a “Blank” item in the selection criteria. From a usability standpoint you might not want this item shown. Or maybe you have multiple items in the slicer that you would like to hide from the report consumers. The video, linked below, walks you through why the “Blank” item is shown and how to remove it.
Video on Adding Filters to a Slicer
Additional Slicer Materials
If you want to read more about syncing slicers check the official documentation release from the Microsoft Power BI Blog. This feature was originally released in February of 2018 and was announced here in the Power BI Blog.
Thanks for watching. If you like this content be sure to subscribe and follow along for more content.
Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn where I will post all the announcements for new tutorials and content.
There are often questions surrounding Publish to Web. What is it? How can I use it to share my reports? This video walks through the proper usage for Publish to Web and how to manage the Power BI tenant settings.
Check out this video explaining the full details surrounding this topic:
Update: This tool has been deprecated as of 2024-11-27. You can now find this as a downloadable HTML file at the following Github page.
In April of 2018 the Microsoft team released the ability to edit the Linguistic schema in Power BI desktop. For those who are not aware of the linguistics, essentially, this is the code that drives how Power BI can interpret your data model when you use Q & A. The linguistic schema is defining how the computer is able to figure out the best visual relating to your question. In Power BI desktop you can double click on the white space of a report page and then the Q & A prompt appears. Then type a statement into the Q & A box, this in turn generates a visual.
In both the Desktop program and in the PowerBI.com service, Q & A is an impressive feature. By default the Power BI desktop creates a linguistics schema about the data model. However, there are some details that the linguistics schema can’t detect. This is where you come in. In the Power BI Desktop you can download the Linguistics file, make any number of changes or additions to the file and then re-upload the file back to Power BI Desktop. But, there is a slight catch. The downloaded files can be quite large and a little difficult to navigate. PowerBI.Tips to the rescue.
Lingo is a web app that allows you to upload your linguistics schema into an easy to use editor. It includes search, code validation, and code blocks that you can use to make writing code easier. Check out the video below to see how it works:
For the full details on the linguistics schema visit the following article from Microsoft. A sample Power BI file, Linguistics model, and Linguistics Spec can be downloaded here. Well that about wraps it up, thanks for reading and happy coding.
If you like what you learned about today and want to stay updated, please follow me on Twitter, Linkedin, and YouTube for the latest updates.
Of all the connection types, I’ve always gravitated towards this one. I imagine it is because I come from the database developer side of things. I’m a big fan of doing things one time, and having one version that controls many reports is extremely appealing. In fact, this topic was my first blog on Power BI so long ago (here for those feeling nostalgic). The live connection is the most powerful of all the connections that Power BI has to offer in my opinion.
Before we dive into the deep stuff, are you aware that you can use this connection type without your own instance of Analysis Services? Let me explain. Anyone who uses the “Power BI Service” connector that was first made available in April 2017 and released to GA in August 2017 is using a live connection to an Analysis Services Instance hosted in in your Power BI tenant. In fact, each time you build a Power BI report in the Desktop, you are building a Tabular model that is then created in the cloud upon publish! This live connection method allows you to gain a bit more control. You can deploy a single dataset to the Service and re-use it to build multiple reports! Having your own instance of Analysis Services on premises or Azure lets you maximize your development and deployment efforts and truly create a sustainable reporting solution.
The evolution of a Power BI solution “should” typically land in a space where a centralized or several centralized models are being used as the backbone for the vast majority of Power BI reports. This centralized approach is imperative in order for large scale BI initiatives to be successful. SQL Server Analysis Services Tabular is the typical implementation that I see most often employed due to the relational nature, compression, in memory storage and speed. That being said, lets dive into the details of what a centralized model gives us, and the pros & cons of the Power BI Live Connection.
Cons:
Most limiting of all in terms of disabling Power BI features.
Desktop Live Connection
Notice: the Data and Relationships icons are not visible after making a Live Connection.
This is without a doubt the most intimidating to the end user that isn’t familiar with the live connection. As with Direct Query, there are features and capabilities in the Power BI Desktop that are just flat out turned off or completely gone. ETL / M / Query Editor? Gone. Data pane, DAX tables, Calculated columns? Gone. Power BI has become only the front end of the process. The expectation is that you are doing all the data mashup / ETL and modeling behind the scenes and as such, these features are all removed.
However, a really great addition in the May 2017 Desktop release was the addition of allowing measures to be created on top of the live connection. This means that if you have a couple measures you need to add to a single report, you can easily add those in the Power BI Desktop without the need to have them added to the model.
Cost
Without a doubt one of the most appealing aspects of Power BI is the price. It is amazing the amount of power and value you get for $10/month. (Desktop is free, but let’s just call it $10 because you need a Pro license to share). When you scale up and start to use enterprise level tools you need to look at the costs that those include. This post won’t go into details because there is a myriad of options out there and the number of options increase exponentially when you start comparing Azure to on-premises. Suffice to say, you’re looking at a much heftier investment regardless.
Different Tools
Another drawback to this connection is that we are now pulled out of the Desktop as a standalone solution and thrown into development areas. At bare minimum we’ll most likely be using Visual Studio, Team Foundation Server for version control, possibly SSIS, SQL Databases and SSAS. Throw in Azure and you might be using Azure SQL Data Warehouse with Azure Data Factory and Azure Analysis Services…
Pros:
Change Control
This feature that can be implemented by Team Foundation Services allows for a developer to manage their code. In the context of the model, this means that I can check in/check out and historically track all the changes to the model. Which in turn allows me to roll back to a previous version, control who has access to the model and secure the access to the model to a known group.
Central model that supports many reports
Hands down the benefit to the model / live connection is that I can build a central model that supports a vast number of reports. This streamlines development, lowers the time to implement changes across all reports from IT and centralizes calculations so that all parties are using the same metrics.
No memory or size constraints in Power BI
Another great feature is that a dedicated server / Azure implementation has the capability to scale up to whatever RAM is necessary to support the model. The limitations of the Desktop are gone, and Power BI capable of handling insanely high volumes of data. This is because the heavy lifting is happening behind the scenes. A prime example of this the new MSFT demonstration that uses 10 billion rows of data related to NY taxis. (Did you catch that? That is a “B” for “BILLION”) I saw it for the first time at PASS Summit 2017, but you can see a quick demo of that below, or here in the first portion of this scale up & diagnostic video.
Now, the underlying hardware in Azure must be immense for this to contain the 9TB of data, but I still think it is amazing that Power BI can provide the same drag and drop experience with quick interaction on a dataset that large. Simply amazing.
More Secure & Better security
Along with the security of being permissioned to access the model there is an extremely valid argument related to security that just make a SSAS model better. The argument is that while the functionality exists in the Power BI Desktop to enable row level security, the vast majority of the time, the report author shouldn’t control access to certain sensitive information. Having that live in a file accessible by others to be modified isn’t something that passes muster in most orgs. With a model that has limited access, change control and tracking, and process for deployment the idea that a DAX function controlling a security access level to information becomes more palatable.
Partitions
This feature enters stage left and it is just “Awesome”. Partitions in a model allow you to process, or NOT process, certain parts of the model independently from one another. This gives an immense amount of flexibility in a large-scale solution and make the overall processing more efficient. Using partitions allows you to only process the information that changes and thus reduce the number of resources, reduce processing, and create an efficient model.
All in all, a lot of this article was about model options behind the scenes, but effectively this is the core of the Live Connection. It is all the underlying Enterprise level tools that are required to effectively use the live connection against a SSAS instance. In some respects, I hope that this gives you some understanding of the complexity and toolsets that are actively being used when you are using Power BI in general. All these technologies are coupled together and streamlined to a clean user-friendly tool that provides its users with immense power and flexibility. I hope you enjoyed this series and that it brought some clarity around the different connection types within Power BI.
Thanks for reading! Be sure to follow me on Twitter and LinkedIn to keep up to date with Power BI related content.
