Forum Discussion
Filter By a List
My company requires me to build a PowerBi report that allows filtering by a list. (I am reasonably porificne tin Power Query, new to PowerBi)
Currrently I am using Power Query which allows a list of account numbers to be pasted into a table, which Power Query then filters by List.Contains. I do something similar for FromDate and ToDate.
In PowerBi my data is Direct Query which doesn't allow for using "List.Contains" functions.
Can anyone suggest how I do this so that when PowerBi is loaded, it automaticlly references the account numbers and required dates? These can be, if needed, in a seperate excel file and formated as required.
I am aware that i can load the accounts number as a dataset into PowerBI and create a relationship between the accounts number fields, and use a slicer. But what this does is limit the account numbers shown and the user still has to select the accounts they want. In some instances there could be 300+ accounts. Adding in the 'Select All' option, for some reason, seems to select all accounts on the main dataset and not just those on the Accounts dataset slicer....
Hope this all makes sense!
I can't obviously share my file as this would be the company sales..
thanks
Barry
Hii BarryWhitelaw
The best approach is to store the required account list in a table accessible to the same source, then create a database view that INNER JOINs the main data with that account table. Connect Power BI to this view, and only the listed accounts will be returned automatically no slicer selection or List.Contains is required. If the list is maintained in Excel, first load or synchronize it into the source database, because cross-source filtering can prevent query folding and reduce DirectQuery performance.
5 Replies
Ideally I would prefer something that would limit the data before it got to PowerBI, in the Power Query side...an alternative to the List.Contains..
Hii BarryWhitelaw
The best approach is to store the required account list in a table accessible to the same source, then create a database view that INNER JOINs the main data with that account table. Connect Power BI to this view, and only the listed accounts will be returned automatically no slicer selection or List.Contains is required. If the list is maintained in Excel, first load or synchronize it into the source database, because cross-source filtering can prevent query folding and reduce DirectQuery performance.
Hi there and thanks for your reply. After some investigation I managed to find that I could use a dax formula, which created a 2nd data table already filtered. This means I can build all my visuals without having to add extra filtering.
It may not be the neatest way of doing it but it works.
I created new table then amended the formula to:
I can't take credit from it, Copilot eventually supplied this after much varied prompting.Barry
Are your filter criteria different each time? If so the dataset still seems like the best option. My question is if there are any other criteria besides account number that you can use for filtering. This would require adding different dimensions to your model and then slicers that filter the main table by those different criteria. Since you say it's sales data I'm guessing there may be more ways to slice other than account number?
Hi there
The filters are generally the same fields each time, Account, Start Date, End Date. As per my previous reply, I did eventually work out how to do this by adding a New Table and amending the code to read as below. This created a filtered set of data, which seems to work perfectly 🙂
thanks for you reply
Barry
