Forum Discussion
Data model and matrix visual advice needed
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
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 :)