Page Comparison

...

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 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 fails.  This 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