Reducing the Need to Repeat Tables Loaded in Power BI Relationhips

Owned by
jrussel1
Last updated: Jun 18, 2019

Scenario 1 - One - One or One - Many:

The data model in question has a fact table that references the same dimension multiple times.  In the following scenario, KEY_TR_BEGIN_DAY, KEY_TR_CREATED_DAY, KEY_TR_END_DAY, etc. all are foreign keys that point to the primary key for the table D_DAY.  The limitation for Power Bi is that there can only be one active relationship.  In the picture below there is one column that is solid (active), and the rest are gray dotted lines (inactive).  One solution to this is to re-import the same dimension multiple times and join them all to the fact via active relationships.   The reason this is not a good idea is because it makes the data model much larger and takes longer to refresh the data.  


Solution:

Create a measure within the fact that has the value in the dimension that you would like to retrieve.  The measure is defined by a lookupvalue funciton.  The lookupvalue function has 3 elements in function call. 


The elements are in order:

the value to return from the dimension table

the value to lookup by in the dimension table

the value that matches the lookup in the fact table.

Example:

Travel Created Day = LOOKUPVALUE(D_DAY[CALENDAR_DAY], D_DAY[KEY_DAY],F_TRAVEL_REQUEST[KEY_TR_CREATED_DAY])

D_DAY[CALENDAR_DAY] - the value you want to retrieve from the dimension table

D_DAY[KEY_DAY] - the primary key in the dimension table

F_TRAVEL_REQUEST[KEY_TR_CREATED_DAY] - the foreign key in the fact table

Issues:

  • This will work regardless if there is an active or inactive connection in the data model.  
  • This will only work in the case of a one-to-many or a one-to-one relationship

Benefits:

  • Smaller data model
  • Faster data refresh during development of a report
  • Data model easier to read and understand

Drawbacks:

  • Relationships between data may not be explicit, as the relationship is defined in part of the measure.


Scenario 2 - Many to Many:

The data model in question has a fact table that references the same dimension multiple times.  In the following scenario, LASTACTION_MDM_ID, and TR_CREATED_BY_MDM_ID, etc. all are foreign keys that point to the a key for the table MDM_CURR_EMPL_V.  The limitation for Power Bi is that there can only be one active relationship.  In the picture below there is one column that is solid (active), and the rest are gray dotted lines (inactive).  One solution to this is to re-import the same dimension multiple times and join them all to the fact via active relationships.   The reason this is not a good idea is because it makes the data model much larger and takes longer to refresh the data.  In this case because the data is not a guaranteed one-to-one, or one-to-many a vslookup function will return more than one result.  The MDM_CURR_EMPL_V table shows multiple MDM_ID(s) for every employee appointment, so each MDM_ID returns more than one row. 



Solution:

A new measure at the fact table is once again needed that will pull a specific value from the associated dimension table.  The difficulty lies in determining what would make the return value be reduced to a single row.  The following example measure below is one way of reducing the return result set to a singular row.  This requires the use of the following functions: calculate, selectedvalue, filter, and userelationship.

Example:

LASTACTION_MDM_ID_NAME = 
CALCULATE(
  SELECTEDVALUE(MDM_CURR_EMPL_V[NAME_DISPLAY],"None"),
  FILTER(
     SUMMARIZE(MDM_CURR_EMPL_V,MDM_CURR_EMPL_V[DATA_SOURCE]),
     MDM_CURR_EMPL_V[DATA_SOURCE] = "GEMS"
  ),
  USERELATIONSHIP(MDM_CURR_EMPL_V[MDM_ID],F_TRAVEL_REQUEST[LASTACTION_MDM_ID])
)


Calculate is needed because if forces the code inside the parenthesis to run and return a value. 

The first element in calculate is:

Selected Value needs 2 values the field to use, and if null the alternate value.  In the above function the return should be the MDM_CURR_EMPL_V[NAME_DISPLAY] and if null the "NONE"

The second element is the filter function:

Filter is used to be able to apply some logic that will reduce the set down to one element.   In this case the filter needs 2 arguments (what to return, and what relationship to use)

The first element above is then limited by the summarize function (to apply a distinct and only return one value if there are multiple that are the same).  In the above case it chooses a distinct on MDM_CURR_EMPL_V[DATA_SOURCE].  

The second element is the limit on  MDM_CURR_EMPL_V[DATA_SOURCE] - in this case it limits it to being equal to 'GEMS'

The third element to the calculate function is what relationship to use.  

MDM_CURR_EMPL_V[MDM_ID],F_TRAVEL_REQUEST[LASTACTION_MDM_ID]


Issues:

  • The use of the code is complicated, and it is easy to make a mistake.
  • The connection (even if inactive) has to be in the relationship model.
  • The data has to be well understood.   In this case you would have to know that the data will only return one person's name if the data_source = 'GEMS' .  If there is a chance that this will return multiple results this code returns the wrong value.

The above code is the equivalent of the following sql code:

select distinct name_display
from wh_usr.mdm_curr_empl_v, wh_usr.f_travel_request
where data_source = 'GEMS'
and wh_usr.f_travel_request.mdm_id = wh_usr.mdm_curr_empl_v.mdm_id