Forum Discussion

arelf27's avatar
arelf27
Icon for Helper II rankHelper II
8 years ago
Solved

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 displa...
  • Seward12533's avatar
    Seward12533
    8 years ago

    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