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
So, I am not sure what I'm missing here, but it may just be a difference in how Power Query and Power BI aggregate data. I have a table with the fields "Username", "FirstName", "LastName", "LoginDateTime" and "Account".
When I "Group By" in Power Query by all but the "LoginDateTime" field and then put in a filter of "< 5" on the resulting "Count" field, I get 81 records.
If I take the query I am referencing in Power Query and build the same table visual and put a visual filter on ("< 5"), I get 69 records.
Why would that be?
Solved! Go to Solution.
With hundredths of a second apart it will depend on the format used and you may need to convert it to use it the visual correctly. The solution here might help:
Power Query will work a lot better with this without any additional formatting for a visual so i'd recommend using the aggregation there.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
I can't post any sample output w/o violating privacy. That said, please just trust that Power BI is leaving out records for no discernible reason.
What is left out seems to be arbitrary, at least to me.
Hi,
Does the visual table have some summarization on any of the fields? Is it possible to see the data or PBIX file?
I would always use the Power Query data if you aggregating it there, if only for the for performance.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Hello @SDittmannFleet
Table visual will only show unique rows, that might be reason for your issue. Can you check which rows are present in power query and not in table visual.
Regards,
Naveen
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Also, if there is a difference and I have to live with it, which one do I go with?
I'd start by comparing the lists to see which ones are missing, i'd imagine that the unique datetime field should make it so that there's no duplicate records?
Once you've identified which are missing you'll be able to either figure out what's causing the issue or at least know which is correct to decide which to go with. I'd guess that Power Query is the accurate one but without seeing the data and what's happening it's a best guess.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
These are the first 20 entries. Names are fake and emails redacted, but the "Lookup" column reflects a quick MATCH between what the aggregated table produces in Power Query and what I get when I use the aggregation in the visual (count distinct) and then filter the visual to give me anybody with a logon count of < 5. The only thing I can think of that causes this is that Power BI for some reason cannot discern between hundredths of a second when doing the count. The entries that are missing are people that have logons literally one right after the other, a hundredth of a second apart. Thoughts?
+-------------+--------------+-------------------------+-------+--------+
| First Name | Last Name | Username | Count | Lookup |
+-------------+--------------+-------------------------+-------+--------+
| Allen | Flores | someuser@somedomain.com | 3 | #N/A |
| Alyssa | Poole | someuser@somedomain.com | 1 | 1 |
| Bobbi | Karpus | someuser@somedomain.com | 1 | #N/A |
| Brandie | Rucker | someuser@somedomain.com | 1 | 2 |
| Brent | Strachan | someuser@somedomain.com | 1 | #N/A |
| Brian | Olesko | someuser@somedomain.com | 3 | 4 |
| Bruce | Marshall | someuser@somedomain.com | 3 | 5 |
| Bryan | Holt | someuser@somedomain.com | 1 | #N/A |
| Bryan | Stuehrenberg | someuser@somedomain.com | 2 | #N/A |
| Cami | Hunt | someuser@somedomain.com | 1 | 6 |
| Chris | Ginest | someuser@somedomain.com | 1 | 7 |
| Christopher | Tulga | someuser@somedomain.com | 3 | 8 |
| Courtney | Chafer | someuser@somedomain.com | 2 | 9 |
| Courtney | Watson | someuser@somedomain.com | 1 | #N/A |
| Dana | Bower | someuser@somedomain.com | 1 | 10 |
| Daniel | Jasak | someuser@somedomain.com | 3 | 12 |
| Daniel | Kozlowski | someuser@somedomain.com | 4 | 13 |
| Daryl | Marske | someuser@somedomain.com | 3 | 14 |
| David | Lee | someuser@somedomain.com | 2 | 15 |
| Derek | Russell | someuser@somedomain.com | 4 | 16 |
+-------------+--------------+-------------------------+-------+--------+
With hundredths of a second apart it will depend on the format used and you may need to convert it to use it the visual correctly. The solution here might help:
Power Query will work a lot better with this without any additional formatting for a visual so i'd recommend using the aggregation there.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
The dates are all formatted as "General Date" or "Date/time". I'll simply keep this at the back of my head that there may be issues and assume that what I can do in Power Query takes precedence. Thanks!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |