Complex Overlay Filters

I would like to set up a dimension table for “Dim Overlay”. This table will store Overlay ID, Overlay Name.

An overlay, per our company’s definition, is a somewhat irregular sales territory that is based on individual lines on an order rather than an entire order.

Example:
Dell Team: Filter lines to Manufacturer = "Dell", Manufacturer = "Dell RSA", Manufacturer = "VMWare", or Vendor = "Dell Federal Systems, LLC"

I have many fact tables to consider, however, Manufacturer and Vendor are line-level dimensions and are therefore connected to every single line fact table.

I’m trying to determine how to build this complex filter which is based on a dimension table. A generalized data model diagram is below.

GAI Data Model Filter Propagation.pdf (65.6 KB)

The simplest way I can imagine would be to connect Dim Overlay via bridge tables to Dim Manufacturer and Dim Vendor. However, I don’t think I can build bridge tables to Manufacturers and Vendors because of the OR relationship of the example. I need to show lines where EITHER the Manufacturer is Dell OR the Vendor is Dell, and these bridge tables would filter out all cases where only one condition is met.

A more complex method would be to build a bridge table from Dim Overlay to Fact Sales_Order_Line, but due to the structure of my data model, I would need to build an additional bridge table for Fact Sales_Invoice_Line, Fact Sales_Credit_Memo_Line, Fact Purchase_Order_Line, Fact Sales_Shipment_Line, etc…

My line fact tables do not interconnect, just the headers. I would need a bridge table for every single line table.

Perhaps there is a very elegant way to build this, but I am not seeing it. The hurdle I can’t get over is the OR relationship with the filters in the example.

Hi @CStaich, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi , we’ve noticed that no response has been received from you since the 11th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

BUMP!

I am no longer thinking bridge tables are viable, as I would need lots of individual bridges to accomplish this goal.

Perhaps I will create a special base measure for each of my overlays (there are only 2 at this point in time, though I was attempting to make an easily expansible solution).

Any direction would be greatly appreciated, and if the problem is unclear please ask questions.

It’s difficult to add much value without seeing some of the things mentioned above.

Images, pbix file, data etc.

Please include examples of these if you would like someone to look into this more.

Also check out the Analyst Cloud if you feel you need private consulting on this.

https://analystcloud.enterprisedna.co/

Thanks
Sam