Filter out of 2 tables

[Ernests]

Hi Experts,

I got a data model where I have 4 tables:

• Product
• Amounts of product
• Store
• Product/amount table from another system.

The relations are something like this:

At the end I need to compare product amounts between 2 systems and use filter Product name from both of these tables.

I would like to have one filter Product where I choose option and it filters the necessary data in both tables.

Right now for each Store I have data something like this:

Are there any solution?

Thanks!

[HeshamKhja1]

Hi @Ernests ,

Yes, this is possible.

But first I have a small comment that these two dimensions are actually different. Even if they have same values, but they are different in concept; that is one refers to product system A other refers to product system B. Hence, having two different filters might be easier for the user to understand and control.

Anyhow to achieve what you are requesting, (Assuming you have one table at the end joined from the 4 tables, I will call it MainTable), follow the steps below:

    1. In load editor, create a field in the MainTable that is a composite field <CompProductName> of both ProductNameTable1 and ProductNameTable2.

ProductNameTable1 & '-' & ProductNameTable2 AS CompProductName

    2. In load editor, create a new table from resident MainTable that has two fields CompProductName, and MasterProductName. Distinct so the master has no duplicate records.

MasterProduct:
LOAD DISTINCT
    CompProductName,
    SubField(CompProductName, '-') AS MasterProductName
RESIDENT MainTable;

   3. Link MainTable and MasterProduct through CompProductKey.

Now if you select any value from MasterProductName, it should filter as you desire.

[Ernests]

Hi,

Thank you for reply!

But in my case your mentioned MainTable is Store table which contains only information about distinct stores like store id, type, status, etc. If I would add CompProductName it would change the count of rows and Store id would not be unique anymore to join amount of products and Product/amount table from another system tables. Also I would have to go through all visuals and measures. 

Maybe there is some other way?

Thanks.

[HeshamKhja1]

Hmm, there could be another way. But I am afraid it might not be as clean.

But before we go there, can you please share a screenshot of your data model?

[Ernests]

Sorry for my bad editing skills but here is the model. Red circle mark the Product names.

Also I wanted to mention that table Product another system contains product amounts as well but Product and Product amount tables have these values stored separately.

Data relationships:

• Product another system to Store table is many to one
• Store to Product amount is one to many
• Product amount to Product is one to one.

[HeshamKhja1]

Okay. The way I am thinking is having an island master table that is not connected to the model, then using chart expressions to control the selection.

So steps to do it is:

1. Create the master product table by loading both product names and concatenation.
   

   MasterProduct:
   LOAD DISTINCT
   	ProductName1 AS MasterProductName
   RESIDENT MainTable;
   
   Concatenate
   LOAD DISTINCT
   	ProductName2 AS MasterProductName
   RESIDENT MainTable;​

2. In the table you created and mentioned in your first post, change the first dimension Store to be:
   

   =If(ProductName1 = MasterProductName OR ProductName2 = MasterProductName, Store)​

3. In table options of dimension Store, untick the option Include Null Values.
   

Now if you have a filter pane MasterProductName, when you choose a certain value it should show the value from both columns.

Let me know if it works.

[Kaushik2020]

May be like below ? 

[WaltShpuntoff]

Instead of keeping the tables separate, why not combine them?

Concatenate the "other" store product/amount into your existing table. You can tag them with a field to tell them apart. Do the same for the stores, again tagging them so you know the internal vs external ones.

That makes product a common dimension, and you can easily do comparisons between stores - both internal and external.

[Ernests]

Thank you everyone for the involvement. At the end I changed the requirements for the task and it fits client desires.

Regarding your proposed solutions:

@HeshamKhja1 I tried this one but I got error in table that the request exceeds the memory limit.

@Kaushik2020 this was not solution in my case because that excludes Product from another system in some cases.

@WaltShpuntoff Then I would have to change all app measures but if I would make this app from scratch I would do that. Also maybe would be some problems using Store Id because each system uses separate key.

Anyway many thanks!