Scatter Chart Ignoring Visual Level Filter Measure

[BIPowerUser]

I am having an issue with Scatter Chart's (Standard Visual and custom from marketplace) ignoring the Visual Level Filter measure in place, causing a 1,000,000 row limit error for a single visual using DirectQuery.  The measure works fine as a Visual Level Filter on a Map, Table and Stacked Column Chart, but appears to be ingnored by the Scatter Chart.

Here are the 3 elements I am using to filter data down, with the final measure used as a Visual Level Filter.

1.) A [Distance] measure for calculating miles between 2 sets of Lat/Lon coordinates. 

2.) A 'Distance' table (1-25) containing a variable [Distance Away] that is used as a Slicer for setting the distance range.  

3.) InRange = IF([Distance]<=MAX('Distance'[Distance Away]),1,0)

An [In Range] measure for a binary flag (1/0) if the TO and FROM lat/lon's are in range of the the [Distance Away] slicer.  When looking at raw data in a table and not using this measure as a Visual Level Filter, it works as expected and is dynamic when adjusting the range of the slicer. 

 

When you use this measure as a Visual Level Filter, it will only show the top 4 records in the table.  When you use this as a Visual level filter on a Map, it only shows the 4 records.  However, when using it as a Visual Level Filter on a Scatter Chart it will throw the 1,000,000 row error, even though you can see the row counts above and it is only 7,260 records. 

The Impala dataset it is connected to is 2.7 billion rows, but utlizing this measure works to filter all visuals except a Scatter Chart.  Any ideas of why Scatter Chart's are ignoring this measure or any other suggestions would be much appreciated.

Thanks!

[v-piga-msft]

Hi @BIPowerUser,

I have made a test with a simple data sample on the latest version of Power BI Desktop (2.65.5313.1381), it seems that everything works as expected.

I get data from SQL Server with Direct Query and then create the measure.

Here is my test result.

Please check if you have installed the latest version of Power BI Desktop.

If you still need help, please share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[BIPowerUser]

I don't think I can share a dummy .pbix file, as you will not have access to any of the underlying data sources (composite model with DirectQuery). 

Everthing worked fine when I used a small dataset, but after connecting to an Impala server with 2.7B rows, that's when the visual started throwing the error.  I also tried Scatter Chart by Akvelon and Spline Chart from the marketplace, same error.  Switch the same visual to a line or bar chart and it loads fine.  It is as if Power BI tries to pull in all the data first, then use the measure to filter and throws an error first.

-BIPowerUser

[jtownsend21]

I believe your statement "It is as if Power BI tries to pull in all the data first" is correct. 

The if statement evaluates each line, try a switch function. That fixed a similar issue I had before. 

Try something like this: 

Switch( TRUE(), [Distance] <= MAX('Distance'[Distance Away]),1,0)

[BIPowerUser]

I am receiving the same 1M row errror with the SWITCH function as I am with an IF statement.  I am also receiving the error when charting the same Depth and Value combo in a line chart.  How do I get Power BI or a DAX statement to filter the data and avoid this error?

In DAX studio, if I am interpreting this correctly, I am seeing that the query actualy ignores the Measure used to filter the data on the inner query.  Then tries to apply it on the outer query, but throws a 1M row error before it can filter the results.