Power Pivot - Applying a filter on a lookup table so that it applies to a table not directly linked to that lookup table.

[mollypeake]

Hey Guys,

I've only recently learnt how to use power query / power pivot etc. I have a situation where I have two large tables of data that are linked via a placement ID. I have created a placement ID lookup, as well as a date lookup. I have also made a venture lookup which is based off information that is only available in one of the two tables. Since the two tables are linked via the placement ID lookup, can they also be linked via the venture lookup? I would like to filter my subsequent power pivot on this venture lookup but the filter is only applied to the table from which the lookup was formed and not the other table. Is there a way I can link these?

Thanks

 

[pjmorris]

Hi Molly, you can only have one relationship active at a time - others can be defined, but only one active. Inactive relationships can be enacted using CrossFilter. The other way, possibly, is to use Table Cross Filtering (sounds like I know what I'm talking about - but trust me I'm only a bit in front of you!). So if you were calculating a sum on the table without the venture listed I think you would write the measure something like this:

[nonVentureMeasure]=CALCULATE(Sum(tblNonVentures[number], tblVentures)

the column [number] is simply the numeric column you want to summarise from the table with our ventures listed. The tblVentures refers to the entire table containing the list of Ventures.

There are a number of useful blogs on the subject, but I've looked at them several times and still dont really know how it works. But it solved a problem for me.

A good read is PowerPivot and PowerBI by Rob Collie and Avichal Singh.

HTH

 

[mollypeake]

Thank you for your help! Much appreciated.