How to join 2 tables conditionally

In a project, more often than none that it is desired to join a dimension table to a fact table based on certain conditions instead of a direct join.

For example, I have a time dimension table that keeps two calendar codes and we only want the data that its calendar code is '001' to be matching with the data in the fact table, otherwise for certain product or location, it's measure would be counting both calendar code.

Now before we proceed, let's look at another option of doing this. We can apply a filter on the time dimension table to filter out all the records that it's calendar code is '001' and then join this table to the fact. Note that if done this way, then the filter will be applied to this dimension and will not be changed. So what if this time dimension is a part of a conformed dimension that joins to two fact tables that one fact that does need both calendar code to be counted? Therefore, we will see if we can change the join logic between the time dimension and the fact table.

So let's look at the below data model:


So let's change the join between Retail Sales Calendar Time and A_RTL_Sales_Audit_AGG

Instead of using a foreign key join, let's use a complex join in physical layer (Yes, I just broke the best practice rule again!):



In the expression builder, enter the following code:
CASE WHEN SUBSTRING("Retail Sales Calendar Time".RETAIL_YEAR_WEEK_TYPE_CD FROM 7 FOR 3) = '001' THEN "Retail Sales Calendar Time".RETAIL_WEEK_ENDING_DATE END = A_RTL_SALES_AUDIT_AGG.CW_ENDING_DATE

Here what I am doing is looking up the column "Retail Sales Calendar Time".RETAIL_YEAR_WEEK_TYPE_CD and if the last 3 digits of that column is '001', then use the column "Retail Sales Calendar Time".RETAIL_WEEK_ENDING_DATE as the joining column, otherwise the join will not return any matching data.

Let's run some test:



And the resulting SQL generated by this query:


However, if the report is using measures from another fact table that doesn't want this condition to be applied:



Then the SQL generated as a result will not have the above condition applied to the query:



Thanks
Until next time!