Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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)

 

model.PNG

 

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:

 

tables.PNG

 

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:

 

related.PNG

 

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 9
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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.

https://www.kasperonbi.com/tune-your-powerpivot-dax-query-dont-use-the-entire-table-in-a-filter-and-...

Anonymous
Not applicable

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks @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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Thanks @MFelix - v helpful!

Anonymous
Not applicable

Awesome!

 

Thank you so much for replying, @MFelix - this worked perfectly.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.