Daniel Otykier on 22 May 2019 15:31:05
Most competing BI tools have a feature that lets you take the output of one query to use as a filter for another query. For an analyst, this is a very valuable feature. Unfortunately, Power BI does not provides this functionality, unless you're prepared to write some complicated DAX.
Consider the following scenario:
You have a table visual that shows you a list of customers that bought a certain product in a certain period of time. This is very easy to achieve in Power BI, by dragging the CustomerKey field and [Sales Amount] measure into a table visual, while also filtering on Product and Time (using slicers or visual/page level filters).
Now imagine you want to know what other products these customers bought. Essentially, you want to add a new filter to the page, consisting of the CustomerKeys currently visible in the table visual. Once this filter is in place, we can remove the selection on our Product slicer to see all sales in the given time period, by the same group of customers. Then, if we slice by a different product, we would see only those customers that bought BOTH the initially selected product, and the product that we selected now. This would basically let us perform a Basket Analysis on our data without writing a single line of DAX code.
Today, we can ctrl+click to select individual items in a table visual, and then right-click to "include" the selected items in a visual level filter. However, if you have more than just a few items, ctrl+clicking each of them is very annoying and time consuming. Also, we need a way to create the "include" filter at the page or report level, instead of only at the visual level.
Ideally, we should be able to right-click on a column header in a matrix/table visual, to add a new filter at the visual, page or report level, based on all the current values in the column (even those that appear outside the currently visible scroll portion). This option should only be available when the "Show items with no data"-setting on the field is unselected (otherwise, we would be filtering ALL values of the field, which does nothing). For practical reasons, we might need to have an upper limit to the size of the filter that can be generated this way, as the generated DAX query would need to hold all the values in the filter.
- Comments (2)
RE: Filter by visible values in a matrix/table column
I am in a similar situation too. We have data model where filters are applied to find the half hour interval over a 20 year horizon where a certain electrical property occurs. We then want to load the output of all generators, interconnectors and regional demand in the Network for that particular interval. Currently we've got a combination of heavy DAX and sub-optimal table relationships to make this happen, but it would be lovely if there was an option to use the output of a measure to filter another visual.
RE: Filter by visible values in a matrix/table column
The idea was actually inspired by a client who works in healthcare: She had a dataset containing a single table with patient diagnoses. If a patient had multiple diagnoses, he/she would appear with multiple records in the table.
Her #1 request was this: Given a list of patients obtained by filtering on a specific diagnosis, what are the top 3 *other* diagnoses present on those patients? Today, she works around this issue by manually exporting the list of patients into a .csv file, which she then re-imports using PowerQuery, to use as a lookup/filter table on the original diagnoses-table in the dataset. This is rather cumbersome and time consuming, as she needs to perform this export/import operation whenever she wants to consider a new list of patients.
An option to create a new filter based on the column of an existing matrix/table visual, would solve this problem in a very elegant way.