Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tktmastr
Helper I
Helper I

Data Missing When Filtering

Hello,

 

I'm not quite sure how to explain this, but I have a table of data from Google Analytics that I need to filter by a couple of things.

 

My filters are "Page" contains "ccauth" and "Event Action" is equal to TM PV. However, when I do that the #'s for each date are not correct.

 

The date of 12/3/2019 should have 89 rows, which is correct if I filter by date, then Page, then Event Action.

 

However, if I filter by Page, then Event Action, and then date is 12/3/2019, I only get 20 rows.

 

I think I'm missing something, but why would it matter the order of filtering?

 

Thanks

 

DateFirst_89Rows.pngDateLast_20Rows.png

1 ACCEPTED SOLUTION

In case anyone else has the same issue, it seems Google Analytics uses "sampling" with a large dataset and does not show everything.

 

https://support.google.com/analytics/answer/2637192?hl=en

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @tktmastr 

Based on your description, i created data to reproduce your scenario. Actually, the result is not associated with the order of filters. Even though the orders of filters differ from each other, the results are the same.

 

You can try to use a step which includes three filters. You can click ‘Edit Query’, go to Query Editor=>Query Settings, right-click the last step, choose ‘Insert Step After’.

r1.png

 

Then insert the formula into the step.

 

     Table.SelectRows(#"Added Item", each [Date] = #date(2019,12,3) and Text.Contains([Page],"ccauth") and [Event Action] = "TM PV")

 

If it still receives the wrong result, maybe there is something wrong in other steps. If I misunderstand your thoughts, please show us your sample data. I am glad to solve the problem for you.

 

 

Best Regards,

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-alq-msft ,

 

Thanks for taking a look. I tried that and didn't get the desired result. 

 

It doesn't seem to pull all of the data unless it's filtered by a specific date first (but I want all dates). Here is a link to sample PBIX file: https://saratogaperforming-my.sharepoint.com/:u:/g/personal/ebrower_spac_org/EUruWtuvQW9GizR5Pc5IkRw...

 

12/3/19 was my example - I'm currently expecting about 85 rows, but getting 20.

 

If you just look through the data for that date where Event Action equals TM PV and Page contains ccauth, you only see 20 rows. But if you filter by date 12/3/19 first, then by Event Action and Page, you get 85 rows.

 

Thanks!

Just a suggestion, but have you checked into the date statement? Could it be a simple difference of european date vs american date format in your data load or filter. maybe the interpretation of date is misaligned?

Not sure why GA would pull in a variety of dates... it seems to be fine when filtering by date first - in fact, that's the only way to get the desired # of rows. It doesn't give all of the rows of data unless it's filtered to a date.

Wondering if anyone has any ideas on why this is happening?

 

Thanks

In case anyone else has the same issue, it seems Google Analytics uses "sampling" with a large dataset and does not show everything.

 

https://support.google.com/analytics/answer/2637192?hl=en

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.