Solved: Data Missing When Filtering

tktmastr · ‎12-11-2019

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

tktmastr · ‎01-03-2020

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

v-alq-msft · ‎12-12-2019

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’.

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.

tktmastr · ‎12-12-2019

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!

KEITHO · ‎12-12-2019

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?

tktmastr · ‎12-12-2019

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.