CODEWITHSUNDEEP
powerbidax
dougp
dougp

Reputation: 3089

Power BI - Filters on all pages not honored

Just starting with Power BI...

Using Power BI with a DirectQuery connection to a SQL Server database. (I doubt SQL Server is relevant to the question, but if it is I'll add it to the tags. Just leave a comment.)

I have created a "New column" defined as:

FirstOfMonth = DATEVALUE('FiscalMonth'[CalYr] & "-" & RIGHT("0" & 'FiscalMonth'[CalMonthNum], 2) & "-01")

I'm using a data mart that was developed using Kimball-methodology star schemas. One thing that means is that there are exception records for the dimensions. So a partial piece of the FiscalMonth table may look like this:

+---------------+--------+-------------+--------------+---------+
| FiscalMonthId | CalYr  | CalMonthNum | CalMonthName | CalYrMo |
+---------------+--------+-------------+--------------+---------+
|             0 | <NULL> | <NULL>      | DWUnk        | DWUnk   |
|             1 | <NULL> | <NULL>      | DWNA         | DWNA    |
|             2 | <NULL> | <NULL>      | DWNH         | DWNH    |
|             3 | <NULL> | <NULL>      | DWNR         | DWNR    |
|             4 | <NULL> | <NULL>      | DWErr        | DWErr   |
|             5 | <NULL> | <NULL>      | DWSys        | DWSys   |
|             6 | <NULL> | <NULL>      |              |         |
|          5275 | 1991   | 7           | July         | 199107  |
|          5276 | 1991   | 8           | August       | 199108  |
|          5277 | 1991   | 9           | September    | 199109  |
+---------------+--------+-------------+--------------+---------+

As you can see, CalYr or CalMonthNum may be null in certain circumstances. But I know that the data I need won't involve any of the exception records (ID range 0-99), so I added a filter to the "Filter on all pages" section in hopes of keeping that from being a problem.

FiscalMonthId is greater than 99

This works fine, until...

I have put two visualizations on the page. One is a line chart and the other is a table. They both use the same columns (FirstOfMonth is calculated as above, ProgramCode and ExpenditureAmount are directly from the database). When I click on one vis to filter it, the other one disappears with a message:

Can't display the visual. See details

When I click on See details, I get:

Couldn't load the data for this visual
Conversion failed when converting date and/or time from character string.. The exception was raised by the IDataReader interface.

But there are no data values used by my DATEVALUE function (within the context of my global filters) that would cause this problem.

The same thing happens when I try to add a measure. I have tried a few different things. Most recently adding a filter to the TOTALYTD function:

Measure = TOTALYTD(sum(ExpenditureMonthlySummaryFact[ExpenditureAmount]),'FiscalMonth'[FirstOfMonth],'FiscalMonth'[FiscalMonthId]>99)

When I add the measure to a vis, I get the same error message as above.

It looks like Power BI is ignoring my global filter when performing any kind of calculation or filter.

Short of changing our data mart design policies, how can I get past this?

Upvotes: 3

Views: 525

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40304

Instead of doing a report level filter, I'd recommend filtering out any data you know you'll never use in your report as part of the query step (in the Query Editor).

This should still work fine with a direct query since it knows how to fold a filter into the SQL query that is passes back to the server.

Upvotes: 3

Related Questions