Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I need to filter out the duplicates from one column in a direct query in power query so that my DAX measures for another column doesn't take duplicate values. How would I go about doing this.
The other post that had a solution, I think does not solve my problem since I am not merging tables together.
Solved! Go to Solution.
Hi @allen3just
If you have "today" filter in Power Query to limit the data loaded into Data model, then have filters in data model like (visual level filter, report level filter,, ect),
In this scenario, doing "today" filter before others is useful.
If "today" filter and your other filters are all in Power Query, there is no difference to doing it before or after.
If your requirement is to limit only today's data showing on Power BI,
For a better performance, you can limit data when connecting with SQL Server,
you could use SQL Statement in Connection window/code.
https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716
https://community.powerbi.com/t5/Desktop/Direct-Query-Filters-in-the-report/td-p/512860
Hi @allen3just
When "remove duplicates" in Power Query, it doesn't support in direct query mode.
According to your last post,
Do you want to sum the "duration" dax column which is created as suggested here?
It is wrong because its type is text and can't convert to date/time format.
Could you tell me what final result you wanted?
For example
name seconds duration
a 100 00:01:40
b 120 00:02:00
a
Hello,
I have already figured out the solution thank you for checking up on this. One minor question and I'm not sure if this is the place to ask. If I have a direct query and I'm doing the filter by today in power query, I notice that the query takes a long time to load. Is it better to do all my filters before doing the filter by today or start with filter by today before doing the other filters?
Hi @allen3just
If you have "today" filter in Power Query to limit the data loaded into Data model, then have filters in data model like (visual level filter, report level filter,, ect),
In this scenario, doing "today" filter before others is useful.
If "today" filter and your other filters are all in Power Query, there is no difference to doing it before or after.
If your requirement is to limit only today's data showing on Power BI,
For a better performance, you can limit data when connecting with SQL Server,
you could use SQL Statement in Connection window/code.
https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716
https://community.powerbi.com/t5/Desktop/Direct-Query-Filters-in-the-report/td-p/512860
Thanks for your help!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |