Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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.
It works but I'm not sure I understand it fully - could you possibly tell me how the SELECTEDVALUE function works here to make the filtering right?
Hello @charliedata ,
The seLECETEDVALUE function returns the value of the sales ID that is referred to in the line, so basically for each line I'm comparing if there is a value in the sales ID override talbe and if it does return that value if there is not, I will return the standard value also of the selected sales ID.
Note that for the grouped calculation of the sum of values you need to use this measure within and aggregator formula as SUMX.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @MFelix - we discovered that Synapse wasn't configured properly and we were using measures where we should have used calculated columns
Performance is fixed now!
Hello @charliedata ,
The seLECETEDVALUE function returns the value of the sales ID that is referred to in the line, so basically for each line I'm comparing if there is a value in the sales ID override talbe and if it does return that value if there is not, I will return the standard value also of the selected sales ID.
Note that for the grouped calculation of the sum of values you need to use this measure within and aggregator formula as SUMX.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |