Hi Mark,

Are you talking about filtering in Power BI or in Excel? I ask because this question is in the Power BI forum, but you mention 'Advanced Filter' which is Excel.

Are you filtering to restrict the data that you have available in your model, or do you need all of the data and want to filter as part of your analysis?

Mynda

Mynda,

Thanks for responding. I'm referring to PBID, not Excel.

In this instance I am trying to implement a 'Page Level' filter where the field to be filtered has over a million values.

In the 'Filters' section of PBID, after I add the field, I have the option to choose 'Basic Filtering', wherein I scroll though the list of values, selecting the ones I want as I go. The second option is 'Advanced' Filtering', which allows me to select values based on a purely 'Boolean' expression (And/Or), but this only allows two operands in the Boolean expression, which is kind of useless for what I am trying to do. The attached screen shot depicts these two options.

The screen shot is from an experimental report using fake data which I generated Excel 'random' functions.

As for the existing BI Forum post on the same subject, I will need to search harder as I didn't find it on first pass

Update: I found this post, but it may not be the one you are referring to - it seems a bit oblique to what I'm trying to do:

Thanks,

Mark.

Hi Mark,

Thanks for clarifying.

In the real world those 1M+ department codes would be grouped into a hierarchy and you would filter at an upper level of the hierarchy. e.g. Departments AA1 to AA50 might relate to a particular region, or another higher level department. And you would apply filters at the region level, or next level of departments. It would be unmanageable to do it any other way. You certainly wouldn't be reporting all 1M+ departments in the one dashboard. They would have to be aggregated somehow. In which case the basic filtering and advanced filtering options would be adequate at this higher level of the hierarchy.

Hope that makes sense.

Mynda

P.S. the forum post I referred to earlier is this post. I wanted to clarify that you were asking about Power BI filters and not Excel. Sorry for the confusion.

Mynda,

Thanks for your further reply 😉

Actually the 'Department Code' example is from randomly generated data and not exactly the same as my real life case. I tried to create a useful example, but obviously missed the point a bit 🙁

I don't have the luxury of being able to establish a hierarchy, since my real data source is a live database cube that is not editable.

My real data has a field called 'Organization Codes', which are variable length numeric, alphanumeric and alpha strings.

Given that I am unable to impose a hierarchy, is there any other way to do this, or am I stuck with trawling through over a million values to get the maybe six or a dozen that I actually want?

P.S. You are correct - I would not be utilizing 1m+ department codes in one dashboard. My aim is to reduce that number to only those I am interested in, via filtering.

Best regards,

Mark.

Hi Mark,

If you only want a dozen or so codes then you'd do this filtering out in Power Query instead of bringing all of the data into your Power Pivot model in Power BI. The idea is to keep your model lean, agile and responsive by only bringing in the data you need. In Power Query you can search in the filter drop down for the codes you want. I guess once you get to Power BI it expects you to have more structure to your data, so searching isn't necessary.

I suppose you could create your own hierarchy, as such, by creating a dimension table that contains only those codes you want. You'd then use the Organization Codes field from the dimension table in your reports...but this isn't really the correct way to go about it.

Mynda