Forum Discussion
Lookups when RELATED doesn't work
I need to use multiple lookup tables to calculate a sales bonus, in a composite model with both DQ and Import tables. The solutions I've seen elsewhere, using RELATED or FILTER or LOOKUPVALUE, haven't worked so far.
Greg_Deckler and ImkeF have written about lookups / merge solutions but I can't figure out how to apply them here
My model has three relevant tables: Sales (DirectQuery from Azure); Sale bonus default (also DQ); Sale bonus override (in Import mode)
Every sale has a sale_id and bonus_accelerator_id, and corresponding bonus_accelerator in the sale bonus default table. Some sales have a corresponding bonus_accelerator_override in the sale bonus override table.
The bonus logic is:
- IF a sale has a bonus_accelerator_override, use that
- ELSE use the (default) bonus_accelerator
- THEN multiply the sale_value by the correct bonus accelerator
The problem I'm having is that I can't find any DAX which can do this multiple lookup logic. I've reduced the data set to illustrate in tables, and here I've just selected the fields rather than calculate a column or measure:
I tried using a calculated column but RELATED doesn't work between the DirectQuery table and the Import table, even though the relationship is defined:
The solution would allow me to have a correctly calculated bonus accelerator, like this:
| sale_id | sale_value | bonus_accelerator_id | bonus_accelerator_CALC |
|-------------|------------|---------------------------|------------------------|
| CHOICE05720 | 195,000 | 2019 Q2Choice - Direct... | 1.5 |
| CHOICE05927 | 130,000 | 2019 Q2Choice - Direct... | 1.0 |
PS. Anonymous - this is hopefully a clearer explanation of the problem in my earlier post
9 Replies
Hi Anonymous ,
Try the following measure:
Measure = VAR bonus_override = CALCULATE ( MAX ( 'Sales & override bonus'[bonus_accelerator] ); FILTER ( ALL ( 'Sales & override bonus'[sale_ID] ); 'Sales & override bonus'[sale_ID] = SELECTEDVALUE ( Sales[sale_ID] ) ) ) VAR bonus_default = CALCULATE ( MAX ( 'Sales & default bonus'[bonus_accelerator] ); FILTER ( ALL ( 'Sales & default bonus'[sale_ID] ); 'Sales & default bonus'[sale_ID] = SELECTEDVALUE ( Sales[sale_ID] ) ) ) RETURN IF(bonus_override = BLANK(); bonus_default;bonus_override)Be aware that your information is very reduce so this may not be efficient.
Hi Anonymous ,
The SELECETEDVALUE funcion return the value of the sales ID you arre refering on the line, so basically for each line I'm comparing if there is a value on the override talbe of the Sales ID and if yes return that value if there isn't I will return the standard value also of the selected sales ID.
Be aware that you for grouped calculation of the sum of values you need to use this measure within and aggregator formula like SUMX.
MFelix - you're the best person to ask for help on this!
When I use this formula against the dataset, I run out of memory ("visual has exceeded the available resources"). Do you know if there's a different way to solve the problem that doesn't take a much memory for the calculation?
Perhaps it's the use of FILTER...? The Sales table is quite big.
