More data is returned than expected when using a page filter in Power BI Desktop

[micheledubose]

Hello,

I have a tabular model project where I've importing data by writing the below query.

SELECT myDateColumn, phoneNumber, myDurationColumn
FROM myTable
WHERE
(
myDateColumn BETWEEN to_date('08-01-2016 02:00:00','dd-mm-yyyy hh24:mi:ss') AND to_date('08-01-2016 02:10:00','dd-mm-yyyy hh24:mi:ss')
AND
myDurationColumn > 0
)

I've created a Power BI Desktop report and connected to the SSAS data source.  The results in my report are correct. In that, what's returned is 13 records.  In the query below, I remove the myDateColumn expression from the WHERE clause. In my Power BI Desktop report, I add a page filter like the image below, however, what's returned is more than 13 records.  I'm still learning how Power BI and SSAS work, so could someone explain the reason for these behaviors?  My requirement is to not have the myDateColumn filtered in the query but to create a page filter where users can choose which date from myDateColumn to select.

SELECT myDateColumn, phoneNumber, myDurationColumn
FROM myTable
WHERE
(

myDurationColumn > 0
)

[parry2k]

@micheledubose wow, glad you figured it out, certain things are very hard to figure out without having the full picture.

Enjoy!!

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[micheledubose]

@parry2k 

Yes.  Sorry for the confusion and thank you for your help.

[micheledubose · Accepted solution]

I've realized my error.  I was not testing apples to apples.  I was comparing test results from this query:

SELECT myDateColumn, phoneNumber, myDurationColumn
FROM myTable
WHERE
(
myDateColumn BETWEEN to_date('08-01-2016 00:00:00','dd-mm-yyyy hh24:mi:ss') AND to_date('08-01-2016 00:10:00','dd-mm-yyyy hh24:mi:ss')
AND
myDurationColumn > 0
)

and this query:

SELECT myDateColumn, phoneNumber, myDurationColumn
FROM myTable

LEFT JOIN table2

ON myTable.column_name = table2.column_name;
WHERE
(
myDurationColumn > 0
)

So when I ran the report, the second query returned unexpected results because of the join.

[parry2k]

@micheledubose isn't it supposed to be AM than PM?

It is at the point where I'm out of ideas, if you can share pbix file (remove sensitive information) before sharing, I can take a look. It shouldn't be this hard, there is something else going on and I have a fear that you are not comparing apple to apple, but I have no way of knowing what is going on.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[micheledubose]

@parry2k ,

Most of the data in my .pbix file is sensitive but I will see what I can share.  I do have another question however.  I tried testing the data by filtering on a different date range.  It seems to be duplicate records which could explain the difference in count of rows.  In the image below, next to date: 1/16/2016 12:00:45 AM, there's a number "3".  This displays on the report that has the page filter.  However, for the report that connects to the SQL query where date is filtered, 1/16/2016 12:00:45 AM has a number "1" next to it.  Does this mean that there's 3 records with 1/16/2016 12:00:45 AM data on the report where I have the page filter for date?

Report display with page filtering date:

[v-easonf-msft]

Hi , @micheledubose 

Yes, this mean that there's 3 records with 1/16/2016 12:00:45 AM data on  your original  table. 

I conducted related tests regarding your question, but did not find any related issues.

Did you miss other filter conditions that led to inconsistent results？

Best Regards,
Community Support Team _ Eason

[parry2k]

@micheledubose doesn't make sense, you have time in your query but in the filter, you have not specified time and also it should not be on or before, it should be before only, you don't want to include 09/01 data, why you have on or before, it should before only.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[micheledubose]

Hi @parry2k ,

I do not see an "on" condition.  I've added the time in my filter for 01/08/2016 as such and still get more data than expected.  

I've also tried the below and still do not get the expected result.

[parry2k]

@micheledubose in SQL Statement you are getting data between :

08-01-2020 2:00 AM to 08-01-2020 2:10AM

whereas in filter your range is :

08-01-2020 to 09-01-2020 (basically two days data) which is a larger range than in your SQL statement.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[micheledubose]

@parry2k ,

In the page filter in my report, if I choose 01/08/2016 as the end date, I do not get results back. Hence, choosing 01/09/2016 instead.

[HotChilli]

Am i missing something?   Shouldn't the test be done with the same dates/times in the filter as the SQL statement?

[micheledubose]

Hi @HotChilli ,

The filter date range in my report is 01/08/2016 to 01/09/2016 because if I select 01/08/2016 to 01/08/2016, I do not get results back. Hence, selecting 01/09/2016 instead.