We were excited to welcome a good friend of PowerBI.Tips Alex Powers to speak with us at this months PUG meeting. Alex blew a bunch of minds by diving into the depths of Calculated Columns and the love or hate relationship we should have with them.
Calculated Columns are a common element to the new users. Those users typically come from a default analyst mindset of “see the data” then perform the calculation. This pattern can lead to a bunch of performance issues. Alex shows us how to be intentional with our decision making and provides some solid advice throughout. Be sure to check out this video as it is packed with great insights and conversation around this topic for the beginner as well as the long time expert.
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
Check out the new Merch!
Hasta La Vista Data
Go Ahead Make My Data
PBIX Hat
Like and Follow
If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.
As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:
This month we got the Power BI desktop update a little bit later because it was released during the 2020 Ignite conference. As always the MVPs are super excited to talk about the latest Power BI features.
This month’s release was packed with all sorts of new features. For a full list of features read the official Microsoft Blog post. Some of our favorite features that the MVPs talked about are:
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
What does it take to design and create a great looking report in Power BI? Do you have a clear understanding of what tools and techniques you should use every time? Recently the owners of PowerBI.Tips had the pleasure of speaking with Reza Rad’s user group on the fundamentals we use to build great looking reports every day.
This presentation focuses on the basic concepts and things we do for every report. This one isn’t about all the unique features we love talking about leveraging, but rather the nuts and bolts that you can use to make every report shine.
Key Concepts
We’d invite you to join us by watching the video where we cover these fundementals in depth.
Discover insights. Who is your audience and what questions can you ask to drive into those key metrics
Define and outline the process for developing key visuals based on those insights
How can you create and use a background to enhance the look & feel of your report
Creating a consistent theme that you can re-use again and again
Using tools like Themes, Scrims or Layouts to help you make this experience more achievable
Designing a Great Power BI Report
If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.
Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
This tip outlines an easy deployment method for data tables that have been manually added to a model via “Enter data” in Power BI Desktop. This is a very effective method especially as it relates to large model deployments. The scenario is typically seen when you build data tables to support slicer/measure interactions. As those selections are made, each one of the values will generate a different DAX calculation to drive a different insight.
An example of that setup would look like this.
Create a data table
First, manually create a data table for slicer selections.
Create Measure and Slicer
Create a Measure that uses separate calculations ,and reference the data table in our slicer to determine which measure we want to display.
As seen above, the slicer has the value from the manually entered data. The id’s correspond to the measures you will want to display in the report visual.
This pattern gives an amazing amount of flexibility to use the same visual and look at different metrics within the same visual. This removes the need for bookmarks and more visuals for each calculation.
Typically, I deploy all my metadata changes via ALM toolkit. (I love that tool). However the slicer options and calculations in this method have a data element that needs to be refreshed as well. Which suggests I need to refresh the data model with these types of updates. However, I don’t want to refresh the entire model. So, I opened up my connection to my model in the Power BI Service to investigate.
You can connect to your Power BI workspace Analysis Services models by following these steps.
Connect to Analysis Services Model in Power BI
First ensure you have the XMLA endpoint enabled appropriately. This is under Settings > Admin Portal > Capacity Settings > (Select Capacity) > More Options > Workloads
Once we have the capacity setup up. Copy the link from your Premium Workspace. Click on the ellipses next to the workspace and select Workspace settings.
Under Settings Copy the Workspace Connection
Open SQL Server Management Studio and apply the connection string in the Server name: location. (Remove the ending of “Initial Catalog” if it is present initially. Set the Authentication to AAD-Universal with MFA and enter your user name (email address) in that section.
Locate the Code
After getting hooked up, my “There has got to be a way” hat went on and I started digging into the objects in SQL Server Management Studio (SSMS).
Mike and I noticed that the entire Power Query query is part of the connection within the table definition. Part of that query is the binary string used when a manual data table is created. Could it be, we could update the local PBIX, grab that binary code and replace the binary in this table!? “YES!”
Here is how you do that.
Right click on the table name > select “Script Table as” > select “CREATE OR REPLACE to” > select “New Query Editor Window” (SSMS hates screenshots apparently, so I could not snag them)
Now that you have the query open, scroll down till you see your Power Query code. Search for the very begining where you see the binary for your data table. Replace this binary string with the new string you have in your PBIX file.
Pay close attention that the binary is wrapped in ‘\’ in the front and before the double quotes at the end. You have to keep it that way or it will break your code.
Execute the query
Refresh your Power BI dataset table
Right click and select Process Table to refresh the data in the table. Choose Process Data
BAM! You’ve just updated the table in the Service without the need to refresh the entire model!
As a result of these findings, I’m very interested in what other parts of the Power Query connections we can update/modify. I think I’ll be exploring those further in the coming months. I am really excited that Power BI has moved onto the Tabular Object Model and we now have the ability to use XMLA read/write. There is so much flexibility and speed in our deployments now! We’re looking forward to finding new and interesting ways to push changes without the data. We hope you find this tip useful, thanks for reading.
Requirements for the above tutorial to work:
Be using a Premium Capacity
In your Power BI Desktop, enable the enhanced metadata format
Toggle on the XMLA Read/Write in the Power BI Service (described above)
If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.
As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:
