Forum Discussion
Dynamically filter down to similar data based on user pick
Not looking for a simple solution so any suggestions are welcome..
We're looking to build a Peer report. Currently the user can pick from various slicers to get to one line of data that is displayed in a table and a few graphs.. (Example: Insurance Name = "A Company", Market Value = $5,000, Industry = P/C) So the user wants to be able to know the peer companies of the above pick, which would be Companies with the same Industry of P/C, a Market Value within some range +- of $5,000, etc.
So how can I accomplish dynamically pulling back some data based on similar characteristics of the data already filtered out by customer? (I know there are no parameters in PowerBI, but prob. I can use DAX measures, etc. to know what the data has been filtered down to, then use measures to build a table with some criteria... But I think there would still be a question of how to filter what's on screen to this data table? Esp. since you can only use one column for key and can't tie tables on multiple key columns..)
Can share some representative sample data?
Sticking with your example since its a good one for using Multiple Fact Tables and forcing relationships bleow is an example, However I would never do what your doing, Rather I would have single table with all the data and use DAX to display all the ones within a range around the selected on and filter out the selected one but the DAX is more complicated so this may be easier until you learn more. I would provide an exmaple if you can share a representative data set.
In this example I have two tables with different companies bridged by Industries and Sectors. I have a slicer for Companies in Base Sales Table and two Table Visuals The left shows you results from the 'Base Sales' Table based on the filter selection and the other shows Results from the 'Comp Sales' Table where the Industries and Markets are the same.
The relationships are unidirectional in the model but I force the filter context (i.e Market and Sector) from the Filtered Table 2 by specifying that table in a calculate (as I described in my first post) this forces the relationship between the tables without selecting "Both". You can refine the secondary table function Average Quality COMP with additional calculate terms to only show results where the results are within a certian range etc..
So the measures are
Average Quality BASE = AVERAGE('Base Sales'[Quality])
Average Quality COMP = CALCULATE(AVERAGE('Comp Sales'[Quality]),'Base Sales')
https://filedrop.telusa.com/message/hB4EpxpREvtZtc07mZXRA1
19 Replies
You may try adding a separate table for Slicer, apply virtual relationship in DAX expressions and then take advantage of Visual level filters.
You lost me at visual level filters. I already have many filters (slicer), visual, drill-through, etc. on my report... In this case what they want is to not have to click on anything.... Basically when they filter down to their pick, that should guide the automatic pull up of the rest of the rows that have similar characteristics.. The visual level filters imply having to select from more filtering options (not automatic..)
If anyone can provide a sample of a solution that would be very helpful...
UPDATE: So I created a physical table to use as a filter (with select distinct of all the possible values from one column: Industry Type)... Then I duplicated my original table that has all columns of data. The I linked all three together via my filter table and changed the relationship to "Cross filter direction" BOTH.. Then I added two table visuals to the report. So when the data is filtered down in the 1st table, the 2nd table automatically only shows rows that have the relevant Industry Type... So that works....
I also created another filter table for Market Type... The problem is, I can only link either Industry Type table to Table 2 or Market Type table to Table 2.. (This is where the problem is with not being able to use two key fields to link on in PowerBI.....unlike SQL..which I really don't understand why they have this limitation..)
Again I'm doing all this via physical tables... My question is, will using DAX eliminate this problem or not? Any suggestions on how to accomplish this (I know about concatenating columsn, but this seems confusing in this scenario..)
I use multiple key fields to link data all the time. You may need to wrap your measure in a calcualte and explicitly refernce one or more tables to force filter context. This example has a single relationship but you can use multiple.
This is the visual.. So data in V_AMBEST_DATA_PBI gets filtered down (based on other filters customers select)... Then I want to be able to put a table visual on the report cotaining Peer AMBest Data table contents (since there's an active relationship to Peer Market Sector Filter going both ways, the data get's filtered down to contains only Market Sectors in V_AMBEST_DATA_PBI.. I also want this data filtered down to Industry Type... Physical Relationship doesn't work as I get error that only one relationship can exist... What would be my Measure to even just select Company Name from either V_AMBEST_DATA_PBI or Peer AMBest Data (these are duplace tables of each other) so that the data is filtered by both Market Sectors and Industry Types (equal to V_AMBEST_DATA_PBI)
