Set Analysis conflict in Concatenated Table (Dimension vs. Filter)

[muratmert41]

Hi All,

I have a data model where Sales and Inventory tables are concatenated into a single fact table. Both datasets share the same field name: StoreCode

Table Format:

StoreCode ItemCode ColorCode  Sales Inventory

A100          ABCD       red              1000  0

A100         XYZ          blue             500   0

A200         E               black           100    0

Now I would like to calculate Inventory. Currently, I use this formula:

Sum({<MyDate= {'>=$(start_date) <=$(=today_date)'},StoreCode={'WR-ON-008'}>}InventoryQTY)

The Problem: I am using a table with StoreCode as a dimension. I want to show a column for "Warehouse Inventory" specifically for warehouse 'WR-ON-008' regardless of the store on the current row.

When the table row is StoreCode = 'A100', the column returns zero because the engine tries to find rows where StoreCode is both 'A100' and 'WR-ON-008' simultaneously, which is impossible.

I know renaming the field to WarehouseCode in the script would solve this, but my model is too large to refactor right now.

Is there a way to ignore the dimension's selection on StoreCode but force the set analysis filter for 'WR-ON-008' on the same field in the frontend?

[Daniel_Castella]

Hi @muratmert41 

I think you need the following expression:

Sum(Total <ItemCode,ColorCode> {<MyDate= {'>=$(start_date) <=$(=today_date)'},StoreCode={'WR-ON-008'}>}InventoryQTY)

This will display the inventory for the warehouse on all stores depending only on the item code and color code but not on the StoreCode. If other fields are needed, you can add them after the total, or remove the ones set if they are not needed.

Let me know if it works for you.

Kind Regards

Daniel

[marcus_sommer]

It's not really related to a set analysis which means reacting to an (adjusted) selection state else it's conflicting on the row-level. A shortcut for re-designing the data-model might be just to add a new field which harmonized the StoreCode and the WarehouseCode and use this for these views.