User Profile
mjohnston0209
Regular Visitor
Joined 3 years ago
User Widgets
Contributions
Re: Member Filter based on List of Values
jennratten , I understand the second part of your code since it is very similar to what I did already. Will you please walk through the first part? Also, the varlist is very exact. Does it update automatically when variables are used? Below is my output. All names and GP/Locs are fictional.Member Filter based on List of Values
I am trying to use a member filter when filtering a query based on a list of values in an excel worksheet. The list is housed in a table and is variable, meaning it can range from 1 to several items. I have found coding that can member filter based on one specific cell, but this has proven more difficult. The only list filters I have found use a text filter, which significantly slows down the query time. Below is the code for a member filter when referring to one specific cell. The cell is B1, and I named it ProNum. ProNum stands for Project Number. = Table.SelectRows(#"Added Items", each Cube.AttributeMemberId([Projects.Project Number]) = "[Projects].[Project Number].&[" & Text.From(ProNum) & "]" meta [DisplayName = ProNum]) Below is a text filter based on a list of values. I first created a list from the excel table. I then used a text filter based on the Power Query list. = Table.SelectRows(#"Removed Columns", each (List.Contains(ListOfLocGP,[LocGP])=true)) For the above coding to work, a person has to enter into an excel table the office location and global practice they plan on using for each person on a job (i.e. Seattle and Accounting). After that is complete, the queries are refreshed and the list is condensed down into unique items. The final format is Loc.GP (i.e. SEA.ACT). The list is then pulled into a another query and used as a filter on a custom column I created that combined location and global practice. The query shows employees per each location and global practice. I plan on pulling this information into a data validation drop down list that shows only the relevenant names per Loc.GP, considerably narrowing down the list of applicable employee names. Once a name is chosen, that employee's bill level is then listed by their name which is used to calculate project revenue. Any help would be greatly appreciated!
