Forum Discussion
Filtering lines from data table based on values in exclude table
Hello Anonymous
I suggest to do all data modification and prefiltering in Power Query first and do only the final calculations in Power Pivot. I don't know your data source, so it's difficult to propose something. But let's assume you are querying analysis service you could create a MDX-statement to select the data you need, without getting them all in your data model. You could apply there already the filtering criteria you are shown in the table.
Hope this information is of any help
Jimmy
Thanks for taking valuable time out of your day to reply to my request. As I understand it, you are suggesting I reduce the table from something like this (I added a few more rows for clairty):
To something like this:
I think you suggested I do it with an M language power query. I have a book on that but decided to poke around on the internet before I got back to reading it (about 1/2 way through so far). I found something that seems to work:
reduced_exclude_data = FILTER(ALL(table),[submit_date]=CALCULATE(MAX(table[submit_date]),ALLEXCEPT(table,table[md5])))
However, I believe this is a DAX formula to be used in the "New Table" call. Indeed it creates a new table and the table is reduced exactly as I would expect. I only get the unique value of each md5 based on the latest date.
To use this data, I created the link in my data model:
Then I added an all pages filter which seems to be doing the job:
Now my question is... is this a bad approach for some reason? I believe you were suggesting an M query and I believe I have implemented a DAX formula to create this table I would assume that the downside is that I now have an extra table in the machine's memory... the full table and the reduced table? However, I expect this table to be fairly small (~500k lines with these 3 simple columns) so I am thinking that may not be an issue.
Is there a better way? Is this way wrong?
Hello Anonymous ,
if it's working for you its fine. And if the performance is okay.
The downside of only using this, is that you have to import the whole data even if you don't need all of it.
With power query you could limit the amount of data in your data model and depending on your data source is taking place query folding, meaning you get from the datasource only the lines you need.
But if your solution is working, I wouldn't change it.
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
JimmyThanks again for the feedback. So, I wanted to try it with the Power Query method you mention. Doing some studying, I found that the "Group By" functionality held some promise. After working at it for a bit, I came up with the below request and the resultant table which looks correct. Although, I will admit the "min" request on the boolean column of is_valid seems wrong.
Any thoughts on this? I next attempted to use this reduced data in my model and found that it works. I applied the same filter on all pages shown earlier and had no trouble. However, I did encounter one oddity. I simply wanted to display the data in this new table and I now get an error when trying to include the md5 in the table. It looks like this:
Does that make sense?
Edit: No it does not as "Min" only keeps the lowest of true/false... not the one associated with the latest date... back to searching.
I followed this tutorial and was able to get my table looking correct... as the example shows:
https://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group
Still getting that OLE error...
But the table cannot be used in Power BI.