Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create new dimension that matches either A or B dimension

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?

2 REPLIES 2
amitchandak
Super User
Super User

@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/

Anonymous
Not applicable

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:

  • "Type" dimension linked directly to dimensions "Product" and "Color" creates a circular dependency.
  • Crossjoin between "Product" and "Color" would explode the table size.
  • New table using summarize (on fact table) and outer join (on Product & Color table) can be created. However, this leads to a circular dependency error, as I can't break the orginal relationships as these sit within the DirectQuery dataset.
  • Calculated column in the DirectQuery fact table (finding the related Product & Color and determining the type) seems to create performance issues.

Am I overseeing something? Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.