Table linkage

[519241]

Hi,

Need  help on implementing below requirement.

I have  three tables with the table names and columns as below,  requirement is to showcase Product and ProductFamily data in two tables and add the filter on the top on ProductName and BusinessEntity.

Product data should be filtered correctly for the selected Product in the filter and ProductFamily associated with selected Product should be filtered.

And for the Business Entity filter both Product and ProductFamily  data should be filtered for the selected business entity,. Business Entity can be different for Product and ProductFamily

Thanks,

[stevedark]

Hi @519241 

The first thing to note is that you can not have a cyclic reference in your data model, so what you show will certainly not be possible as is.

The second thing is that fields associate when they have the same name, and don't when the names are different. Rename fields to either make, or break, associations.

The best way to deal with what you want is most likely to load the BusinessEntity table twice, renaming the fields to link to the Product and ProductFamily tables respectively.

BusinessEntityProduct:
LOAD
  BSProdKey as ProdBusinessEntity,
  BusinessEntity as [Product Business Entity]
FROM [lib://MyLib/BusinessEntity.qvd] (qvd);

BusinessEntityFamily:
LOAD
  BSProdFmlyKey as ProdFmlyBusinessEntity,
  BusinessEntity as [Product Family Business Entity]
FROM [lib://MyLib/BusinessEntity.qvd] (qvd);

Having one BusinesEntity filter that affects both Product and Product family is more tricky. You may have to load it as a DataIsland (i.e. with no field names common between it and other tables), and then apply the BusinessEntity selection with Set Analysis.

I would try with the method I describe above first, and see if that delivers on your business requirement.

Steve

[519241]

Hi Steve, 

Thanks for your reply, i tried the way you mentioned, created two  separate business entity tables with key column and BusinessEntity column but no luck on how to join it further in single filter. 

Thanks,

Pankaj

[stevedark]

@519241 

In that case, another approach is to load the BusinessEntity table as a data island, not renaming any of the fields so that there is no association with the other tables. In the data model viewer there will not be lines drawn to the other tables and selections made on the BusinessEntity field will not affect the other tables.

To implement the selections will then require all expressions to have some Set Analysis in there, to apply the selection in the data island to the other tables.

So, to get a count of products, based on the Entity selection, it would be:

count({<ProdBusinessEntity=P(BSProdKey)>} DISTINCT ProdName)

And, similarly, to get a count of Product Families it would be:

count({<ProdFmlyBusinessEntity=P(BMProdFmlyKey)>} DISTINCT ProdFmlyName)

The downside of this approach is that the Green/White/Grey will not work properly with the selections, but it will mean the selection in one field can affect multiple tables at the same time.

Hope that works out for you.

[519241]

Thanks Steve, expression will work for getting count.

In my case i am not calculating the count just showing the records available in the Product and Product Family in table. In actual there are many columns in the Product and Product Family tables those all i have to filter on single Business Entity filter.

Is there possibility to work on table if not possible then last option seems will have to use two separate business entity filter for Product and ProductFamily.

Thanks,

Pankaj

[stevedark · Accepted solution]

Hi @519241 

You can have a table with many columns in it and just the one count expression (as above) and the rows in that table will then filter to valid rows based on the selection. You could do this with two tables simultaneously, both based on the same selection. This should give you what you need. Unfortunately with this approach you can not hide the measure that you are using to flag the row, so there will be a column of numbers at the end of the table which you might prefer was not there.

The approach with separate dimension tables though could be more intuitive for users and give them more control.

[519241]

Thanks Steve for your valuable input it helps me a lot. 

Thanks,

Pankaj