Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arelf27
Helper II
Helper II

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..) 

 

 

1 ACCEPTED SOLUTION

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

 

capture20180711171714163.png

 

View solution in original post

19 REPLIES 19
arelf27
Helper II
Helper II

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. 

Untitled.png

 

 

 

How and where do you specify that Cross filter direction should be "Both"? I don't understand what I need to do for my specific scenario to work..  I'm trying to use multiple tables as filter tables. I don't understand your examples (I'm also not a developer, so specific DAX example would really help)... 

 

How would I select a column of Company Name (not a numeric field) so that it only contains Company Names where 1.) Market Sector is equal to Market Sectors in the V_AMBEST_DATA_PBI table and also 2.) Industry Type is equal to Industry Type in the V_AMBEST_DATA_PBI table...  Without specifying Cross filter direction "Both" I'm not getting the right filtering..

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

 

capture20180711171714163.png

 

This worked. What I was initially confused about was non-amount fields like Company name... Because I thought I needed to filter them out.. However just dragging Company name next to new calculated fields filters them appropriately... Also the reason I had slicer tables and a copy of original table was because I was trying to set the relationships to cross-filter both ways (however that only allowed one active relationship) so once I switched them all back to single I was able to create two active relationships... 

 

I am curous how would you do the DAX if only using one table? BTW seeing a solution in .pbix was very helpful!

Yes, you would write a meausre to for Market Cap but blocking with and IF so it ingors the filter selection but limiting to only withing the Market Cap Range aronnd the selected one.  Build a new visual using this measure. Note it will include the selected one unless you filter it out.  I am willing to help you with the DAX if you share some representative data. 

I don't see a way to attach Excel file...

 

Capture.JPG

I will also need a way to have flexible filters... So show all Company Names where Market Value is within +_ some pre-defined amount (Ex: market value > 10000 and < 300000)

 

Also something to the effect: Count # of Cusips for each Company Name.. Show all Company Names where # of Cusips are within similar range (+_ 100)

 

In the above scenario, is there a way to change that DAX to filter based on the two scenarious like these...

You can extend the solution I provided to add other filter but it does something very simlar. In this case dynamically filtering and selectign other companies within a range of the Average Quality score. The Cusip Score if very straightfordard jsut DISTINCTCOUNT(cussips) and you can use the similar IF logic to block displaying that meausre where you don't want it.  

Sorry to bother you so much, but still having a hard time implementing this.. You're example is close, but not quite what I'm looking for to do... In the attached picture, the client would click on a Company (in this case C1), so the 1st table shows one record, Company: C1, Industry Type: A, Market Sector: I.  The second table should then automatically match records based on Industry Type and Market Sector.. So the 2nd table should now reflect only one record: Company C4, since it matches on Industry Type and Market Sector.. (This is the DAX I'm struggling with.. ) Since my From BASE table is filtered by Company = C1, using your measures I get nowhere since the filter needs to be cleaned first... I've tried to stick ALL function, but can't figure out where it goes...

 

 

In other words, even before I get to calculate measures, the From BASE table has been filtered down to one record (Company = C1) 

 

 

Capture.JPG

In my solution the Base Table is NOT FILTERED BY C1.  My Measure limits the results displayed in the visual to only those that match the selected customer as harvested by the Selected Customer measure. 

 

All you need to do is add logic to the IF statement limiting what is displayed by the [Average Quality within Range] measure to only show records that Marth Industry Type and Market Selector. 

 

Right now its limiting only for ones in the defined range around the upper and lower limits of [Average Quality Selected]

AVG Quality within Range = IF([Average Quality Base]>=[Lower Limit]&&[Average Quality Base]<=[Upper Limit],CALCULATE([Average Quality Base],FILTER('Base Sales','Base Sales'[Company]<>[Selected Company])))

 

So you need to add additional logic. You could do this with one long DAX statments but breaking it up into seperate measures will make it more understandable. 

  • Selected Industry = LOOKUPVALUE('Base Sales'[Industry Type],'Base Sales'[Company],[Selected Company])
  • Selected Sector = LOOKUPVALUE('Base Sales'[Market Sector],'Base Sales'[Company],[Selected Company])
  • AVG Q witin Range and same Industy Sector = CALCULATE([AVG Quality within Range],FILTER('Base Sales',[Industry Type]=[Selected Industry]&&[Market Sector]=[Selected Sector]))

Then replace new [AVG Q Within Ragne and same Idustry Sector] measure in your table. 

 

You could do this all in on long DAX formula preferable with VARiables but the benefit of this approach is you can create dynamic titles like 

 

Page Title = "Report for "&[Selected Company]&" compared to other companies from "&[Selected Industry]&" Industry and "&[Selected Sector]&" Market Sector"

 

Kudo's accepted - I woudl recommend spending some more time learning DAX a great resource is https://www.amazon.com/Power-Pivot-BI-Excel-2010-2016/dp/1615470395/ref=sr_1_1?s=books&ie=UTF8&qid=1...

 

 

To clarify... I think because you're using Company Table that's not connected to the Base Sales table, you can use Filter to say give me all Companies where Company is not equals to selected Company... However in my case, I have 15+ slicers that I allow clients to pick from to narrow down first table results.. They are all fields from Base Sales table... So in this case, if you switch field behind Slicer to Company from Base Sales table instead of a separate Companies table, you will achieve what I have...

Correct, you could replace the simple companies table I have with your more robust one and use whatever filters you want to narrow it down to a selected company.  As long as the company has more than one company selected the measures and tables I built for you will be blank.  As soon as there is a single company selected they will populate.  Its probaly to hard to continue to do this over the forums.  I've invested enough into this already and if you want I will set up 30min call or webex with you.  I sent you a DM wih my contact info.

 

I'm sure there is a way to do it but is hard without having the actual tables and data model to play with. The idea of the blocking measures using IF and conditional tests should work.  Also your data model has to be well defined.  Tip : be sure you slicers are slicing lookup tables and not the fact table  otherwise you can't use ALL to remove the filters. Sounds silly but you need to make lookup tables that cover everything you want to slice on and slice those vs the data table if you want ALL to work. 


@arelf27 wrote:

To clarify... I think because you're using Company Table that's not connected to the Base Sales table, you can use Filter to say give me all Companies where Company is not equals to selected Company... However in my case, I have 15+ slicers that I allow clients to pick from to narrow down first table results.. They are all fields from Base Sales table... So in this case, if you switch field behind Slicer to Company from Base Sales table instead of a separate Companies table, you will achieve what I have...


 

@arelf27@v-chuncz-msftThanks, you need to use drop box or something. I went ahead and did something with the example data I shared with you before.  Ended up needign a disconnected slicer.  I'm sure some real DAX guru could do it without requiring the disconnected slicer but this is what I know how to do. 

 

https://filedrop.telusa.com/message/oYrbqpLkmanSoMNcvLXEqi

 

Here is what I did - Updated Base Table for single companies 

  1. Created Table of just Company Names in PowerQuery by referencing Base Sales and then removing other columns and duplicates
  2. Left as Disconnected Table (had to remove the link PowerBI automatically created)
  3. Created [Selected Company] measure to harvest the user selection - note its NULL if nothing is selected
  4. Created some Measures
    • Average Quality Base = AVERAGE('Base Sales'[Quality])
    • Average Quality Selected = CALCULATE([Average Quality Base],FILTER('Base Sales','Base Sales'[Company]=[Selected Company]))
    • Upper Limit = (1+[Limit])*CALCULATE([Average Quality Selected],all('Base Sales'))
    • Lower Limit = (1-[Limit])*CALCULATE([Average Quality Selected],all('Base Sales'))
    • AVG Quality within Range = IF([Average Quality Base]>=[Lower Limit]&&[Average Quality Base]<=[Upper Limit],CALCULATE([Average Quality Base],FILTER('Base Sales','Base Sales'[Company]<>[Selected Company])))
  5. Created two visuals using [Average Quality Selected] and [AVG Quality within Range] respectively
  6. Added Warning Message to display if no company is selected asking them to select a company.

Now the upper Table shows the selected company and the below one shows any company whose Average Quality is within 20%. I hard coded the 20% but you could easiy add a parameter to harvest that value from a slicer. (New Parmater from Modeling Tab)

Capture.JPG

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) 

 

 

There are also issues with having that many bi-directional filters. I tend not to use them any more but use the trick I mentioned in my previous post to force filter contexts for specific measure calculations.   Once you remove the bi-directional filters you will be able to link both of your filters to both tables. 

 

Also if you need to you can have disconnected copy of the filter and then harvest user selection using SELECTEDVALUE then as part of your measures you can compare if it matches that selection as a condition if measure is calculated or not. 

 

v-chuncz-msft
Community Support
Community Support

@arelf27,

 

You may try adding a separate table for Slicer, apply virtual relationship in DAX expressions and then take advantage of Visual level filters.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If anyone can provide a sample of a solution that would be very helpful...

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..)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.