Archive for category: Blog

As I explained here, Power BI introduced the highly anticipated Quick Measures feature to avoid writing DAX formula for common measures. The feature is still in preview, so make sure you enable it in Power BI Desktop from File, Options and Settings, Options, Preview Features. Many quick measures, such as time intelligence calculations (YTD, QTD, Period Over Period Change, etc.), rely on a date field. The date field could come from a Power BI-provided date hierarchy or a Date table.

Letting Power BI auto-generate date hierarchies is easy but dangerous and limiting. As Chris Webb explains here, Power BI-provided hierarchies could bloat the size of the data model. For example, by disabling them in data in a data model provided by a customer, I was able to reduce the data model size from 300 MB to 30 MB!

Instead, a best practice is to have a Date table. It gives you a control over what date periods you want to include. It also allows you to analyze several fact tables by date in a single visual.

Unfortunately, unlike Power Pivot or Tabular, currently you can’t mark a Date table in Power BI Desktop, even though everything is configured correctly in your model. Attempting to use a date field from a Date table when setting up a quick measure, results in this error.

A “primary date column” means a field of a Date type in a Date table marked as such. DAX time intelligence functions only work when several conditions are met by the date column, such as must be at date granularity, must be continuous, cannot have duplicate values, etc. Since PowerBI hasn’t verified that a user supplied date column meets all those conditions, it doesn’t allow users to define Quick Measures to prevent unexpected results if user is unaware that they cannot just pick any date column.

So why the teaser then? A primary date column will work if you import a model with marked Date table from Power Pivot or if you connect to Tabular. While waiting for Power BI to let us mark Date tables, the workaround is to use a Power BI-provided hierarchy (at least to get the formula), and then change the formula manually to reference the Date field in your Date table. For an example of how to do this, refer to my blog that I mentioned at the beginning. Just don’t give up on data tables!

Power BI security is interwoven with Azure and O365 security. Sure, granting individual users access by entering emails works everywhere but it quickly becomes counterproductive with many users. For example, a large insurance company would like grant report access to 250 users and integrate this report with Dynamics 365. If you secure individually, this will require entering 250 emails three times: Dynamics 365, Power BI, and Row-level Security (RLS). As in the old days, we want to use groups to reduce the maintenance effort because all these users are added to a security group, we can just grant access to the group. And, when users come and leave, we make changes to one place only: the group and everything will work magically. However, nothing is simple and security is no exception. In fact, Office 365 has various group types, as you can see below.

To make things even more interesting, all groups are not created equal and currently Power BI supports has a varying degree of supporting them. Things will undoubtedly become more unified in time but for now the following table is meant to help you understand what’s supported and where.

Let’s start with the features. “App workspace content” means adding group members to a shared app workspace. “App distribution” means publishing an app for a wider distribution, e.g. to the entire organization, as you can do previously with organizational content packs. “Dashboard sharing” means sharing a dashboard by email (the first sharing option in Power BI before workspaces and content packs were introduced). RLS means row-level security, i.e. adding groups as members of an RLS role so that they gain different access to data. Moving to group types, “O365 Group” is the group you’d get when you create a workspace. This type of group is supported by many O365 applications, such as Yammer, SharePoint, Teams, etc. “O365 distribution list” is meant for sending email to multiple users in Outlook Web Access. “O365 Security Group” is Azure Active Directory group (if you sync your on-premises AD to Azure, you’d get O365 security groups too). “Exchange Dynamic Distribution List” allows you to create condition-like groups, e.g. for sending email to everyone.

Are you still with me? One more twist. Due to a bug, app distribution and dashboard sharing requires the O365 security group to have an email assigned. A few more notes:

• Creation of content: App workspaces in Power BI are meant for authors to collaborate and create content. You need a Power BI Pro license for accessing these workspaces and you cannot yet add AD security groups as members/admins to app workspaces
• Distribution of content: Once content is created in an app workspace, you can distribute it in two ways:
  • Share out individual dashboards and reports. If the original app workspace is in premium, recipients just need a free license. If not, they need Pro licenses. Also, you can share dashboards to security groups but not O365 groups.
  • Publish out an app from the workspace. If the original app workspace is in Premium capacity, recipients just need a free license. If not, they need Pro licenses. Also, you can publish an app to AAD security groups but not O365 groups.

TIP: If you have Power BI Premium, you can share a dashboard or report from a Premium workspace to other users. You don’t have to publish an app. This is explained in more details here.

Clear as mud? As I said, I hope that table would become obsolete in a few months when Power BI would support all group types everywhere.

The September release of Power BI Desktop adds two very useful features: drilling through another page and explain increase/decrease. SSRS practitioners might find “page drillthrough” similar to SSRS actions. However, instead of navigating to another report, it allows the user to navigate to another page within the same report (at least for now cross-report drillthrough is not supported). Although not as flexible as SSRS actions, PBI page drillthrough expands the Power BI data exploration capabilities. Previously, you had the following drillthrough options:

1. Dashboard drillthrough – Clicking on a dashboard tile navigates you to the underlying report assuming the tile was produced by pinning a report visual.
2. See records – This chart-level features allows you to right click a bar and see the data behind it.

I won’t repeat the Microsoft intro (watch it here) but I’ll add a few more notes that the demo doesn’t cover and I find very useful. Consider this report which has two pages: Sales page that shows a sales summary and Customer Orders page that is defined as a drillthrough page. The idea here is to allow the user to drill from the sales summary to the Customers Orders to see the data in more detail.

To “define” the Customer Orders page as a drillthrough target, I had to only add CalendarYear and SalesTerritoryCountry fields to the new Drillthrough Filters area in the Visualizations pane. This is conceptually similar to defining parameters in an SSRS drillthrough report. Interestingly, the summary page automatically detects if the visual has one of the fields used in the Drillthrough Filters area. If it does, it automatically enables the Drillthrough context menu. The context menu is activated even if the summary page has just one of the fields defined as drillthrough filters. For example, if the Sales visual has only Calendar Year, the drillthrough page would show all customer orders for that year. If it has also SalesTerritoryCountry, the drillthrough page would show orders for that year and for that country. If the Sales visual has none of the fields used for drillthrough filters, then Drillthrough won’t show up. In other words, Power BI automatically matches the source fields and drillthrough filters and this saves you a lot of configuration steps, such as to configure parameters, to check which field exist and to pass All to the parameters that don’t exist!

Page BI page drillthrough was one of the most requested features by the community. I’d love to see it extended with the following options in future:

1. Expression-based filters – This applies not only to drillthrough filters but the other filter types. For example, it will be great to default the filter to value using an expression, e.g. =IF(SELECTEDVALUE()==”, <some value>).
2. Ability to drill to pages in other reports.
3. Multiple context-aware drillthrough actions – For example, SSRS allows you define an action for each report element. Currently, PBI page drillthrough takes the entire row context and you can’t define drillthrough targets for specific fields.