...
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:
Code Block |
---|
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:
Code Block | ||
---|---|---|
| ||
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 |