Power BI DAX Dynamic Measures

Dynamic measures are an effective way to avoid crowding your report with different versions of the same visual without using bookmarks. No disrespect to the versatility of bookmarks, but they can quickly clog up reports with the amount of visuals used and can be a headache to modify depending on the complexity. This post will explore when to use dynamic measures, how to set up your data model to accommodate them, how to implement them, and limitations.

A primary use case for dynamic measures is when you have multiple business owners looking at a report who each want to see the report in slightly different ways.

For example, you may have three major stakeholders looking at a report: CEO, CFO, and COO. The CEO may be more interested in future profits and looks at trends in the number of loads booked to see where we are headed. The CFO wants to see profit trends on loads that have already been completed (actualized profit). Additionally, the COO would rather see what’s currently in transit and look at only picked up loads. You could make three pages, one based on booked loads for the CEO, another based on delivered loads for the CFO, and one based on picked up loads for the COO. But what happens if your report is already going to be multiple pages and needs room to grow? You would need to triple your page count to accommodate the needs of your users or build three completely separate reports! Don’t worry, dynamic measures can solve this problem for you without clogging up your reports or workspaces with extra measures, visuals, and reports.

In order to use dynamic measures, you will need to set up your data model with some tables for the singular purpose of selecting the dynamic measure. You will also need to have a few measures that will correspond with those filters. For example, if you are want the end users to choose between seeing load count, profit, and revenue, you will need to make a table that contains all three of those fields as rows. The easiest way to do this is to go into the Power Query editor and use the “Enter Data” option. Make sure that your options are exactly as you’d like them displayed for your end users and are in a table that’s easy to reference.

Hit close and apply. Once your new table is loaded, you DO NOT need to connect it to your other data in the data model. Dynamic measures work because they do not rely on relationships between the filter and the data, but on the selection of measures to be used. You have a couple options here, you can build each of these measures separately or build them once within the dynamic measure itself. There are reasons to do both, so we will build out both examples. Having separate measures offers you lots of flexibility down the road, but if you are only using these measures within the dynamic measure then it’s best to keep it all within the dynamic measure to save some space and increase efficiency in your DAX queries.

Building the Dynamic Measure

After you have your desired measures built out, you can reference them inside your dynamic measure either by using separate measures or variables. The key expression in dynamic measures is an IN ALLSELECTED combination. It’s vital to know exactly how your options were spelled inside your recently make custom table because you will need to spell them out within the dynamic measure. The basic syntax is as follows:
Selected Measure =
SWITCH(TRUE(),
“Option1” IN ALLSELECTED(CustomTable[ColumnName]),[Option1Measure],
“Option2” IN ALLSELECTED(CustomTable[ColumnName]),[Option2Measure],
“Option3” IN ALLSELECTED(CustomTable[ColumnName]),[Option3Measure],[DefaultMeasure])

If your dynamic measure is being used to filter a selected field instead of choosing a completely different metric, I recommend using a variation of the following syntax:
Selected Measure =
VAR A =
SWITCH(TRUE(),
“Option1” IN ALLSELECTED(CustomTable[ColumnName]),[Filter1],
“Option2” IN ALLSELECTED(CustomTable[ColumnName]),[Filter2],
“Option3” IN ALLSELECTED(CustomTable[ColumnName]),[Filter3],[DefaultFilter])
RETURN
CALCULATE([Measure],FILTER(Table,Table[FilteredColumn]=A)

Using separate measures:

Using variables:

Using the switch function in conjunction with the IN ALLSELECTED allows the end user to “switch” their selection with the measure associated with it.

Using the Dynamic Measure

To use a dynamic measure, you’ll need to first build a slicer with your custom table you made earlier and referenced inside your dynamic measure.

Depending on how many options you have, it may be wise to format this slicer as a drop down or as a horizontal list to make it appear more like buttons.

To test your dynamic measures, let’s throw it into a card visual then select a couple different options to make sure it’s choosing the correct measure.

Perfect, working like a charm! From here, any visuals you build using these dynamic measures will adjust when users select their metric. Be sure to carefully label your visuals so it’s clear that what they are looking at will change depending on what they have selected. If you would like to have your profit and revenue to have ($) signs in front of the metrics, just build that formatting into the measure itself using FORMAT. In the example below, format is used to set profit and revenue as currency while load count is a standard number (comma separated). For more formatting options see the following documentation from Microsoft: https://docs.microsoft.com/en-us/dax/pre-defined-numeric-formats-for-the-format-function.

Now when you go to build your report, instead of 3 duplicate pages use this dynamic measure in all your visuals and place the slicer at the top of the page for various users to interact with at will.

Power BI, Dataflows, CDM, and Dynamics 365 – My Journey

PBIDataFlowsAndCDMSeries
Journey into Dataflows, Dynamics, and CDM

My company, Pragmatic Works, is working through a Dynamics 365 migration. As part of that migration, we need reports. We have made the decision, seemingly obvious given who we are, to use Power BI for all of our internal reporting. Through the process, I have been working with our migration team a lot and have been tasked to handle key reporting for the consulting team. We are implementing both CRM and PSA (Project Service Automation) as part of the rollout. I am responsible for reporting that supports the non-Sales operations for the consulting organization. This series of posts will follow my journey to get a good solution in place. I will give you the resources I used, the advice from the pros on my team, and anything else I can share to help your journey along as well.

I want to caveat that this is my journey through the process. I am sure some mistakes will be made along the way, but we should all learn together. I am not doing this in a vacuum. I have some very talented Power BI team members helping me with strategy and design. Their contributions will be reflected throughout the process, but want to give them credit now for sure.

Evaluating Power BI Dataflows vs Power BI Shared Datasets

I started the process by trying to determine what is the best option for building data models that can be used for ongoing report creation within our company. I was familiar with shared datasets and with the latest improvements in the service, it was a good place to start. However, I have been talking with the team about Power BI Dataflows (and Azure Data Factory Dataflows, but that is not relevant here). I put it out to the group above to discuss pros and cons. Overall, the team pointed out that Dataflows with Dynamics would be the best fit, however, there is not much out there on Dataflows in action.

Brian Knight and I were having a different conversation about the Common Data Model (CDM) and PowerApps for other projects. During that conversation, Brian also mentioned that the CDM was ideal for working with Dynamics data.

Coming full circle, the team agreed that CDM with Dynamics and Dataflows is a good way to go. And so starts the journey. I will be documenting the research and progress along the way here. I will be posting whenever I can. Here we go!

Power Platform Conference – 7/23-24/2019

Pragmatic Works is hosting a 2-day Power Platform online conference. If you are unable to get out to onsite conference but need some help with Microsoft Flow, PowerApps, or Power BI, this is a great opportunity to learn from some experts from around the world.power-platform-virtual-conference-v04_facebook Information About the Conference

This is a 2-day virtual conference covering Power BI, PowerApps and Flow. The cost is $49 for all sessions, recordings and bonus materials. There will be 12 sessions each day.

 Dates: July 23 & 24, 2019

Times: 7 a.m. to 7 p.m. ET

Website: https://powerplatformconference.com/

I hope to see many of you there virtually!

How Azure Storage Kick Starts Your Big Data Projects

So, your boss says, ‘Let’s do big data!’ And you think: ‘I don’t even know what that means or what I have to do. Do I need big data? Do I need a bunch of servers?’ These are the questions we hear all the time.

A simple intro into the big data world is to take advantage of Azure blob storage. This is a great starting point since when you put data in Azure blob storage, it’s formatted very similar to how you put it into any other Hadoop storage scenario.

Once you get the data there and it’s in your file-based storage, here comes the big question: What am I going to do with this data? As you’re in the introduction phase, start simple. Power BI will connect to your blob storage, and it will connect the same as you would connect to an HDInsight or Hortonworks cluster.

Using Power BI, with limited learning curve and expense, you’ll be able to take advantage of the data you stored there in your beginning big data scenario. It also gives you the chance to start adding on to that, such as looking at an HDInsight or Hortonworks cluster to use and reference the storage, without moving your data around.

Check out Azure Data Week coming October 2018 – www.AzureDataWeek.com.

If your business wants to do big data, this is a great start on the path. If you’re doing other data warehouse work in Azure, you can use Azure blob storage as your staging area. It’s a simple way to begin without worrying about what you need from a server standpoint and infrastructure is eliminated from the equation.

Power BI Data Security – Power BI Report Server

Power BI Security LogoPower BI Report Server was released as a way to host reports on premises. It was one of the highest requested features for Power BI. Power BI Report Server offers a subset of Power BI Service capabilities and as such does not have the rich collaborative or security options as seen in the service (online). I have a number of other thoughts on this tool, but that is not the purpose of this post. I am often asked in sessions on Power BI Data Security about Power BI Report Server. The problem is that Power BI Report Server is not what Power BI was intended to be and as such security is very different.

Power BI Report Server Uses Files and Folder Security Model

Unlike Power BI Service which leverages the Office 365 security model with workspaces and apps, Power BI Report Server only supports deploying Power BI Desktop files as Power BI Reports in SQL Server Reporting Services. Each file is uploaded to the Report Server and it is viewed by opening the report.

Power BI Report Server - Portal

You have essentially three layers of access to the report file security in Power BI Report Server.

  1. The portal itself can be secured. You can and should limit access to the reports by only allowing specific users or groups access to the report portal.
  2. Folders can be used to provide more granular security over a group of assets in the report portal. In the image above, I created a folder called PBI Secure Reports. A specific AD group has access to this folder. If a user does not have permissions to the folder, the folder does not show up in the portal and they cannot access the folder or the assets, including Power BI reports, stored in this folder.
  3. Individual reports can be secured as well. I never recommend this option as it becomes administratively difficult to manage. However, the capability is there is a single asset needs to be secured in this fashion.

These options work for any asset stored in the Report Portal and are not limited to Power BI reports.

Power BI Report Server Report Nuances

If you have read many of my posts around Power BI Data Security, I have discussed gateways, workspaces, and even Office 365 groups. The following items are uniquely related to Power BI reports stored in the report server.

Data Sources

Because it is not possible to use a Reporting Services Shared Data Source with your Power BI reports at this time (not sure if this will change), the data source information will need to be managed by report. This is not dissimilar to the Power BI Service. However, for on premises data we use the gateway with the service. There is not a common or shared data access feature in Power BI Report Server. Data sources are included in the report and can be managed in the deployed report by clicking the ellipses on the icon and selecting manage.

Power BI Report Server - Data Source

Only the report creator, Content Managers, and Publishers have permissions to see and modify this information.

Protecting Data Sources

One concern raised is whether data sources with credentials are downloaded when the Power BI file is downloaded. First key idea is that only users with the Publisher and Content Manager roles can download the files

When a Power BI report file is downloaded, you are prompted for credentials when refreshing data. Passwords are encrypted so they must be reentered when the file is opened or data refreshed in the desktop.

Mapping SSRS Roles to Power BI Functions

The portal, folders, and files are secured using SSRS roles. Here is a high level summary of the roles and how it impacts Power BI Reports

  • Browser – This is similar to a read only function or if you would deliver the report as a Power BI App in the service. This should be used by anyone who needs access to the report, but does not create content.
  • Content Manager – This is for a content creator with admin privileges. This role can manage content and user access.
  • Publisher – This is for content creators who do not need to manage users. Most content creators fall into this category.
  • Report Builder – does not apply to Power BI Reports which use Power BI Desktop to develop reports.

Avoid Deploying to the Portal Home Page

I would recommend you not allow Power BI reports to be deployed to the primary portal, but create folders to manage the group of content creators and managers based on the department or group level needs.

Reports deployed to the home page of the portal should be managed as enterprise assets. Use the folders and related security groups to manage who can deploy at this  level.

Wrap Up

I would like to extend a special thanks to Kathy Vick, Principal Consultant at Pragmatic Works. She provided guidance on implementing Power BI Report Server. Thanks Kathy for the help on this topic. Check out Kathy’s Twitter (@MsKathyV)  and LinkedIn account to learn more.

Check out more about Power BI Data Security in my blog series.