Data model and matrix visual advice needed

[vovcha]

Hi all,

I'm trying to figure out the most sustainable way to shape my data model to allow for a certain visual.

Here's the initial relationship scheme:

Each product can have several components in the Bill of material (BOM). And each component has a certain characteristic.

This is very simplistic model, but it serves the purpose of showing the expected outcome.

And here are the visuals:

What i'd like to have is the matrix 1 (sales per month per product with some extra characteristics in rows, that come from different tables, not only from products), but with all the products that have sales and not only the ones that have some records in the "BOMs" table. I.e. in this example i want to have the sales figures for "Bounty Dark" too. 

Moreover, if i decide to apply an external filter on "Component Group" = 1, i'd still like to see "Bounty dark" with it's sales. 

Another question: is there any way to avoid using bi-directional relationship between "Products" and "BOMs" and still be able to have the fields from "BOMs" in the rows section? 

I understand that there're workarounds such as making calculated columns in the "Products table". 

But i'd like to understand if something is possible with this data structure. And in general is there a best practice how to model this type of data.

Thank you in advance.

Vlad

 PS.

pbix file here: 

https://wetransfer.com/downloads/e26391684320ecb5682af1a1fe8c510720181001143257/8e96efcb2975e9ed5aaf...

[v-frfei-msft]

Hi @vovcha,

I think for the first one question, you can use matrix like this. Expanding the columns that you want.

For the second one, I think we can add calculated column by using LOOKUPVALUE function in the target table.

name = LOOKUPVALUE(Products[Name],Products[ProductID],BOMs[ProductID])

Regards,

Frank

[vovcha]

Hi @v-frfei-msft,

Thanks a lot for your reply. 

I've learned that if you add the row subtotals to the matrix, the "bounty dark" sales start being visible, which is already good. 

However, i still have an issue if i apply an external filter (via slicer or page/visual filter) on the Component Group. If i ask to use only the component group = 1, the bounty dark product completely disappears from the matrix. 

I understand, that when i apply the filter on "Component group" column from "BOMs" table, this filter is propagated (through the bi-directional relationship) to the "Products" table and therefore "Bounty dark" is filtered out. 

Which makes me want to try to remove this bi-directional filtering even more. But then the visual stops working...

Solution with the calculated column indeed is an option (and i've written about it in my original post), but it's just not elegant :).

I'm wondering if there's any better solution without creating this calculated column.

The matrix logic should be:

Show me all the products with their sales, but also show me ComponentID and characteristic, but only for component group = 1, and if there's no component with such group, still show me the sales. 

This is the expected result, simulated manually in excel.

I realize that maybe i'm just asking too much, but who knows - maybe there's a way 🙂