Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a simple star schema here with one fact table and for simplicity say two dimension tables "dim Product" and "dim Color". These sit within a DirectQuery dataset which I can't change, but I am looking to build a new dimension onto it using a composite model.
Objective: find an efficient way to create a dimension that is based on checking both the Product and Color dimension for a string match.
Example of what is needed:
IF( CONTAINSSTRING('dim Product'[Product name], "ABC") || CONTAINSSTRING('dim Color'[Color], "Green"), "Type 1",
IF( CONTAINSSTRING('dim Product'[Product name], "DEF") || CONTAINSSTRING('dim Color'[Color], "Blue"), "Type 2",
IF( CONTAINSSTRING('dim Product'[Product name], "GHI") || CONTAINSSTRING('dim Color'[Color], "Red"), "Type 3")
)
)
Note: there is a logic to the order, in case a product contains "ABC" with "Red" color it should be marked as "Type 1".
However I'm not looking for a value as output, but a "Type" dimension that I can use to filter/slice the fact table.
I've tried adding a calculated column to each dimension table separately and then creating a new "Type" dimension table, however trying to set a relationship between this new table and both dimension tables leads to an error (two paths connecting to the same fact table). As I understand using a calculated column in the fact table itself is not desirable, but if that is an option please note that the fact table only contains the [Product ID] (not the product name in the dimension table) and the [Color ID] (not the color name in the dimension table).
Can someone help find the best way (performance-wise) to create such a "Type" dimension table considering this is a composite model?
@Anonymous , if need work across the table, either you need to bring one value together if they have joined.
if not you can create a new table using generate or crossjoin and can use the filter on top of it
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Thanks @amitchandak . Perhaps I misunderstand, but a crossjoin on Product/Color would explode the size as I have many thousands of products and thousands of colors.
Thoughts on options I investigated so far:
Am I overseeing something? Thanks!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |