Overwriting Power BI Filters

Scenario: You’ve created a dashboard-looking Power BI report that has a filter. Most visuals on a report page need to show only data for the selected filter but some must ignore the filter.

Solution: There are several solutions to achieve this goal depending on requirements. Let’s start with the no-brainer and move down in complexity.

  1. Move the unfiltered visuals to another report page — Slicers and page-level filters filter visuals on the same page (note that the Power BI Desktop February update allows you to configure a report-level slicer). By moving the visuals that should remain unaffected by the slicer to another page, you’re effectively ignoring the page-level filtering.
  2. Use a slicer and “Edit Interactions” – If you use a slicer, you can use the Edit Interactions feature (Format ribbon) to specify which visuals ignore the slicer selection. Edit Interactions is described in more detail here.
  3. Consider using time calculations – For example, if most charts on the report must show data for the last 12 months, but some need to show data for the current month, you can apply the most common filter, which in this case is 12 months. For those charts that shows the last month, create a Month-To-Date (MTD) calculation and bind it to those charts.
  4. Overwrite page-level and report-level filters – Sometimes you can’t use slicers, such as in the case where you need more advanced filtering options or when you need to filter the report by URL (you can pass filters on the report URL only if you have page-level and report-level slicers). This is the most complex case because it requires some changes to the model as I describe below.

Currently, Power BI doesn’t allow you ignore page-level or report-level filters. As you’d quickly find out, Edit Interactions doesn’t work for them. When you apply such a filter, Power BI generates a query with SUMMARIZE that applies the filter as an argument to SUMMARIZE. Think of that filter as a WHERE clause that is applied after all measure-level filters. So, you might have a measure that ignores the filter, but the report would still return the rows matching the filter condition. However, you can use the following workaround to achieve the desired effect. Consider this report.


In this case, a page-level filter filters CalendarYear from the Date table to values 2007 and 2008. The first Table visual returns data only for these years (no issues here). But the Chart visual shows data for all years. Follow these steps to get the Chart visual to work as shown:

You need a new measure to ignore the dimension filter. However, the key to get this to work is to use the field form the fact table on the report.

  1. Add the filter column (CalendarYear in this case) to the fact table. You can do this by different means, such as creating a DAX calculated column, creating a PowerQuery calculated column, or by PowerQuery merged queries (to look up the value from another table). The important thing is that you need to have the filtered field on the fact table.
  2. Use that field to the chart (not the one from the dimension table that the page-level filter uses).
  3. Create a measure that overwrites the filter and use that measure on the chart:

    NetProfitAcrossYears = CALCULATE(SUM(ResellerSales[NetProfit]), ALL(‘Date'[CalendarYear]))

    The measure ignores the filter on the CalendarYear field. This expands the filter context on the table to all years. Because the Year field used on the report is from the fact table, the net effect is that the data for all years is shown. This approach won’t work if the chart shows measures from two fact tables because you probably won’t have a relationship between them.

    In this post, I outlined several options to overwrite Power BI filters. It wouldn’t be nice if Microsoft enhances Edit Interactions to page and report-level filters to avoid changes to the data model.

Visual-level Formatting in Power BI

Scenario: You want to overwrite the formatting of some field in a Power BI Visual, such as in the case of showing the field in one visual with decimals and  then in another without decimals. Or, you might want to overwrite the default format when connected live to a semantic model. You search left and right, bing the Internet, and still no clue as to why Power BI doesn’t have this feature.

Answer (that you probably won’t like): Most visuals already support display units and decimals. Table and matrix would probably get this feature very soon. We don’t have ETA for full custom format string override (per visual). Your vote counts so vote on

As a best practice, I recommend applying format settings in the model so that it’s applied consistently to all reports.

In the narrower case of formatting measures, you can create a new measure and change its formatting. This also works when Power BI Desktop connects to a Tabular semantic model because you can create local DAX measures on top of the semantic model. Just create a measure that piggy-backs on an existing measure, whose format you want to change. Then, use the Formatting section to change its format.


Mark as Data Table in Power BI

In my “Understanding Dates in Power BI Quick Measures” blog, I mentioned that having a Date table is a best practice but Quick Measures didn’t support it. Fortunately, the February update of Power BI Desktop fixes this. Now you can mark a Data table as such, as you’ve been able to do in Excel Power Pivot since the beginning.


Marking a date table accomplishes three things:

  1. Disables the Power BI-generated date hierarchies (aka as inline hierarchies). Good riddance! Check the same blog for details of why inline hierarchies should be avoided.
  2. Allows you to use your Date table for time calculations in Quick Measures.
  3. Makes DAX time calculations work even if the relationship between a fact table and the Date table is created on a field that is not a date field, such as a smart integer key (YYYYMMDD). Previously, you had to create the relationship on a field of Date data type.

Power BI Sharing Got a Bit Better 2

A week ago, I wrote that Power BI sharing improved by giving you the ability to include specific reports and dashboards in an app. Recall that that there are three ways to share Power BI content: workspaces, apps, and dashboard sharing. Only the last two lets you share content out of a workspace, such as by sharing with free users if the workspace is in a Power BI Premium capacity.

The fast-changing cloud world brought in another nice enhancement this week: the ability to share reports, just like you could share dashboards.

Suppose you have published some content to a workspace and want to share some reports with a certain group of users and other reports with another group of users. An app can’t do it because there is a 1:1 relationship between an app and a workspace and in the process of creating an app you specify a fixed list of recipients. However, you can now share these reports individually without having to create a dashboard.

Once you open the report in, just click Share in the top-right corner, and you’ll be presented with the same options as when you share a dashboard. Again like dashboard sharing, you can share reports with external users thanks to the the Power BI B2B integration, as I explain in my last newsletter.


Atlanta MS BI and Power BI Group Meeting on January 29th

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on January 29th at 6:30 PM. Julie Smith will hack sweet reports in Power BI. Innovative Architects will sponsor the meeting. And your humble correspondent will show you two new cool Power BI features: integration with PowerApps and Q&A in Power BI Desktop. For more details, visit our group page.

Presentation: H*ckin Sweet Reports with Power BI
Level: Intermediate
Date: January 29, 2018
Time 6:30 – 8:30 PM ET
Place: South Terraces Building (Auditorium Room)
115 Perimeter Center Place
Atlanta, GA 30346
Overview: Power BI is constantly evolving; this session is an overview of the current elements of Power BI and ways to implement them, based on experience in the field using it. Topics include the Power BI Service, Power BI Desktop, Power BI Embedded, Pricing models and some details of Power BI Premium. Sample data set used in this presentation is from @Dog_Rates on Twitter.
Speaker: Julie Smith has spent close to 20 years moving data using varied tools such as MS Access, MySQL, and SQL Server versions 2000 and on. She is a Business Intelligence Consultant at Innovative Architects, where she has been implementing Power BI v2 and Azure solutions for the last couple of years.
Sponsor: Since 2005, Innovative Architects has been serving industries to help solve business and technology challenges. We provide business consulting, development and implementation services for Microsoft platforms which include BizTalk, SharePoint, .NET, Business Intelligence, Mobile Apps and much more.
Prototypes with Pizza “Power BI Writeback and Desktop Q&A” by Teo Lachev


Q&A in Power BI Desktop

Natural queries (aka Q&A) has been a prominent feature since Power BI Service existed. Wouldn’t be nice to do the same in the Power BI Desktop? You can, but make sure to enable first Q&A from File, Options and Settings, Options (“Preview features”) tab because it’s currently a preview feature. Once you do this and restart Power BI Desktop, you should see “Ask A Question” button in the Home ribbon.

Sometimes the fastest way to get an answer from your data is to ask a question using natural language. For example, “what were total sales last year.” Use Power BI Q&A on the cloud or desktop to explore your data using intuitive, natural language capabilities and receive answers in the form of charts and graphs.

As of this time, Q&A requires data to be imported. It doesn’t work with DirectQuery connections. There are two ways to activate Q&A in Power BI Desktop:

  1. Double-click an empty space anywhere on a page.
  2. Click the “Ask A Question” button in the Home ribbon’s tab.

This adds an empty “Stacked Column Chart” visual and a Q&A area above it that prompts you to ask a question about your data. On the desktop, Q&A works the same way as in Power BI Service. As you type in your question, it guides you through the metadata fields and visualizes the data.


Once you’re done with the question, you can use the visual just like any other visuals. Unlike Power BI Service, once you deactivate the visual, such by clicking somewhere else on the page, the Q&A box disappears, and you can’t bring it back to see what question was asked.

Power BI Writeback

One of the Power BI most prominent strengths is that it’s a part of the much broader Microsoft Data Platform that spans various on-premises and cloud offerings for data storage, processing, and analysis. After the integration with Visio, Power BI just took another leap forward with the introduction of the PowerApps custom visual. Use this visual to bridge the Data Analytics and Developer worlds. Now your Power BI apps can integrate with Line of Business (LOB) applications in versatile ways. Suppose you have a Power BI report that shows sales by customers. As the user browses the data, he realizes that some changes need to be made. Granted, the user can open the appropriate business app and make the changes there, and then go back to the report to see the changes. But read-only reports and are so 20th century. Instead, why can’t we allow the user to make the changes on the report by integrating it with PowerApps? This scenario is commonly referred to as writeback.


This is exactly the approach my Customer Writeback app demonstrates. The user selects a customer in the table and the customer details show up in the Change Customer visual to the right. This is the PowerApps visual that references my Customer Writeback PowerApps app. The user can make changes in the form and save the changes back to the underlying database. Then he can refresh the report to see these changes. If you have experience with PowerApps, implementing this scenario takes minutes. If this is the first time you hear about PowerApps, don’t worry; it’s take much to learn it especially if you have a developer background. Here are the important implementation details:

  1. Implement your Power BI report first. In my case, I implemented the report in Power BI Desktop. I connected live (DirectQuery) to my AdventureWorksDW2012 Azure SQL Database. Connecting live is preferable for writeback reports because the user can just refresh the report to see the updates. If your Power BI Desktop file imports the data, the user would have to wait for the dataset to refresh to sync the data changes.
  2. Deploy the report to Power BI Service ( Currently, only published reports can create new PowerApps apps (Power BI Desktop reports can only connect to existing apps).
  3. Open the report in edit mode. In the Visualizations pane, click the ellipsis (…) button and import the PowerApps custom visual from the Office Store. With the PowerApps visual selected, add the fields that you want to make available to PowerApps. When the report is previewed, Power BI will pass to PowerApps a dataset with these fields.
  4. In the PowerApps visual, click Create New to create a new app. This will open the PowerApps Web Studio, but you prefer developing on the desktop, there is also a desktop version of the PowerApps Studio that you can install from the Microsoft Store.
  5. My app is as simple as it could be. All is needed is a single form that populates its fields from the same AdventureWorksDW2012 database. The form uses the following expression for the Item property. Although Power BI passes a dataset, in my case the dataset has only one record, so the First function returns that record. When you create the app, Power BI would create a PowerBIIntegration custom data source, which is what Power BI uses to pass the data. The expression filters the form source data where CustomerID in DimCustomer table matches the Customer ID field pushed by Power BI.
    First(Filter(‘[dbo].[DimCustomer]’, CustomerAlternateKey = First(PowerBIIntegration.Data).CustomerID ))
  6. Your app can use any of the PowerApps controls to visualize the data, including toggles, dropdowns, data pickers, buttons, and so on. Because the Toggle control expects a Boolean field, I couldn’t just submit the form to update the underlying record. Instead, I had to rely on the Patch function to translate the Boolean value to ‘1’ or ‘0’ because that’s the values the DimCustomer table stores.
    Patch(‘[dbo].[DimCustomer]’, First(Filter(‘[dbo].[DimCustomer]’, CustomerAlternateKey = First(PowerBIIntegration.Data).CustomerID )),{FirstName:DataCardValue2.Text,LastName:DataCardValue3.Text,HouseOwnerFlag:If(Toggle1.Value=true, “1”, “0”), EnglishEducation:Education.Selected.Value})
  7. Preview the app to test it as you go. To test it from the Power BI report, you need to publish the app. Before you go live, don’t forget to share the app with the same users that will view your report. One of nice aspects of the Power BI integration with Power Apps is that the custom visual references to the published app so that you can test the changes as you develop. However, one thing I encountered is that sometimes the changes are not immediately reflected, so I had to navigate off the report and come back to it to test the latest changes.

The Power BI integration with PowerApps opens exiting new possibilities and redefines the meaning of a report. Because PowerApps integrate with Microsoft Flow, you can integrate your reports with “smart” applications that you can implement with almost no code! Featuring more than 100 data sources, you can use PowerApps as an integration hub to mash data from almost any place and then embed PowerApps screens in Power BI reports.

I attach the source code of the Customer Writeback app. You can open it in the desktop version of the PowerApps Studio.

Power BI vs. Tableau (Part 3)


Feeling the heat, Tableau has come up with an updated Power BI vs. Tableau battle card as one of my clients pointed out. This time designed as a video. I guess the previous “10 Ways Power BI Falls Short” slide deck, which I discussed in the part 1 and part 2 of my “Power BI vs. Tableau” blog , wasn’t effective enough. I concur given the large number of customers abandoning the tool. Tableau is desperately trying to breathe new life into their aging software by a series of acquisitions to stay competitive but they’re now fighting an uphill battle. And their marketing materials should have a timestamp because Power BI improves every month and points get outdated quickly (see the first part 1 of my blog).

But let’s take a look at the latest battle card so we are better positioned to answer the question asked by Tableau “Which one helped me answer my questions faster?”

  1. 2:17 minute – Tableau shows a bar multiple by Year and Region, with the narrative “it’s surprising that other tools can’t do that”. Here is a similar visual with Power BI.
  2. 4:00 minute – Maps. OK, Tableau is very excited about lassoing points on the map like it’s the Holy Grail of geospatial analytics. What about other features that in my opinion are more important, such as using authoritative spatial data, like ArcGIS? Because Tableau its proprietary visualization framework that doesn’t natively support plugins, this is what you have to do to get it with Tableau integrated with ArcGIS. With Power BI, it’s built in a custom visual, which by the way supports lassoing. So, which one helped me answer my questions faster?
  3. 5:30 minute — The high-density outlier pitch. Alas, this time isn’t taking the central stage. As explained here, as of 9/30 all Power BI visuals except maps are high density so it’s a mute point. UPDATE 1/8/2017: As of the December 2017 update of Power BI, maps are high-density too.
  4. 7:11 minute – Tableau pitches that the tool can connect to lots of, lots of data but shows less connectors than Power BI. Power BI has 80 connectors and more are added every month.
  5. 8:00 minute – Tableau discusses collaboration and how easy it should be to share with coworkers by deploying to on-premises Tableau server. Well, I’d argue that it’s easier to deploy to the cloud, or even better, give the user the choice to deploy to a PaaS platform or to on-premises server. I also find it amusing that a tool that was once considered a “gold standard” for data visualization has run out of  visualization strengths, and harps on collaboration from this point on.
  6. 10:00 minute – I’m losing the pitch as Tableau is demonstrating how dashboards have full fidelity when published. So, do Power BI reports.
  7. 11:00 minute – Recipients personalizing shared reports and dashboards by creating views. Tableau has a point here, the Power BI sharing story needs consistency. Previously, organizational content packs allowed users to create personal read-write copies but apps, which supersede content packs, don’t have this feature (they are read-only to the recipient). About filtering, users can pass filters on the report URL. And users can create reports from scratch if they have access to the dataset, such as members of a workspace.
  8. 13:12 minute – Sandboxing and lifecycle management. The current Power BI Service story is to use workspaces for different environments, but Power BI Premium would most likely improve on this. On premises, you can use DEV and QA report servers which don’t require licensing. Try to get a free server with Tableau for QA/DEV!
  9. 14:00 minute – Auditing. Power BI supports usage metrics on report and dashboard level. Power BI also has comprehensive and integrated auditing with Office 365.
  10. 15:00 minute – More pitch about the Tableau Server governance. Well, SSRS/Power BI Report Server had this for a long time. But it also supports SSRS traditional reports, Excel reports, and Power BI reports. Speaking of a centralized report management, can you deploy anything else than Tableau reports to the Tableau report server, so it becomes an enterprise report portal for different report types?
  11. 16:00 minute – More about auditing. SSRS/Power BI Report Server has an execution log for this.

Then at the bottom of the page, we have a study by someone ex-Gartner, citing lower TCO for Tableau compared to Power BI. I can’t be reading this right. From the Gartner’s 2017 Magic Quadrant for Business Intelligence and Analytics Platforms, “On an annualized basis, Microsoft Power BI is roughly one-third of the license cost of a three-year perpetual BI license, but 80% lower than other cloud BI products. Low total cost of ownership was cited as the second most important reason for reference customers choosing Microsoft Power BI.” Yet, this researcher has found that “Microsoft Power BI’s total cost of ownership (TCO) to be 29% higher than Tableau”? Pick who to trust. And should business users deal with 20 GB datasets to start with? What about the novel idea of putting all of this data into a centralized semantic layer so we don’t have datasets moving around? Wait, isn’t a Tableau model limited to a single dataset with “data blending” capabilities? What’s the TOC then for all these isolated Tableau spreadmarts?

When you talk to Tableau about Power BI, get them to answer also the points I made in Part 2 of “Power BI vs. Tableau” blog.

Hey Cortana, where is Power BI data?

A Power BI presentation is rarely complete if someone doesn’t ask me to demonstrate the Cortana integration. However, unlike everything else in Power BI that gains features in time, in my opinion the Cortana experience has lost some value. Previously, you could ask natural questions in Cortana across datasets, just like you can use the Power BI Service Q&A to gain insights from dashboards. Unfortunately, Microsoft has removed this feature in favor of searching for report pages that are specifically optimized for Cortana (also known as Cortana answer cards). You can also search dashboards and reports by name but that won’t get you the “vow” effect since 1) you need to know the name and 2) when you click the name Cortana opens Power BI in a separate browser window. So much about natural queries and machine learning. I think the change took place after Microsoft decided to move away from Cortana natural queries to Microsoft’s Azure Search Service.

UPDATE 11/7/2017: I’ve heard from Microsoft about the Cortana change. Previously, you could ask natural questions in Cortana across datasets, just like you can use the Power BI Service Q&A to gain insights from dashboards. However, Microsoft noticed there were a large percentage of cases where customers were enabling Q&A in Cortana simply to search for reports, without intending to ask natural questions. This was resulting in unexpected Q&A ad-hoc results showing up in Cortana answer lists, leading to user confusion. So, Microsoft decided it would be better to restrict results to Cortana answer cards until Power BI supports configuration settings to allow model authors to explicitly control whether ad-hoc answers should be enabled as well.

Back to the Cortana answer cards, they appear to work only for datasets created in Power BI Desktop. They don’t work with the Power BI samples, nor they work in reports connected to dataset created directly in Power BI Service (e.g. by importing an Excel file).

Nevertheless, with the rising popularity of voice technologies (Alexa, Siri, Cortana), you’d probably still get the “vow” effect. And, even better, the reports are now interactive!


Can’t get enough from Cortana or get it to work at all? Try the “Troubleshoot Cortana for Power BI” article at

Power BI Service in Get Data

Scenario: You have published a Power BI Desktop file with a report to Power BI Service. You want to give certain users access to some pages in the report. This is not a security mechanism although it could be, if you want to control security at the report layer.

Solution: When a customer asked about this scenario, my first thought was to create reports directly in Power BI Service. For example, the report that’s included in the Power BI Desktop file could have some visuals that you might want to share with everyone. Then, you create additional reports for each group of users and share them via dashboard sharing. This approach will work but if someone nukes the dataset, your reports go down with it. True, you can export a report connected to a dataset created in Power BI Desktop but who bothers to back up reports on a regular basis?

Then it dawned on me that you can connect Power BI Desktop live to a published dataset, just like you can connect to Analysis Services (recall that datasets published to Power BI Service are hosted in Tabular).

The major benefit of using Get Data->Power BI Service is that you can still back up the Power BI Desktop file and your reports with it.

Another benefit is that you can treat the published dataset as a “semantic layer” and let users create reports from it without importing data. For example, instead of asking users to call a stored procedure passing parameters and importing data, you can export all the required data and publish it to Power BI Service. Then, all users have to do is to connect to the dataset and start creating reports.



Nothing Found

Sorry, no posts matched your criteria