Forum Discussion

Salfii's avatar
Salfii
Advocate IV
8 years ago
Solved

Efficient Lookup

Hello everyone,   I´m currently loading Data from Microsoft Dynamics 365 CRM ONLINE with PowerBI over OData-Feed.   For every option-Set (like a Drop Down Value/List in CRM) OData gives back the ...
Need Help
  • Salfii's avatar
    Salfii
    8 years ago

    Hi v-ljerr-msft,

     

    thanks for the ideas but I want to keep this in Query Editor and do it with M because it´s a modelling-problem.

     

    LOOKUPVALUE looks tempting here, but at least for me, this is only some kind of workaround and might worsen the GUI/DAX-Performance for the enduser, which is more important than the query performance in my eyes. The bad query performance is only anoying at developement time because there is not a high frequecy reload schedule in production.

     

    I´m now using another way which involves "unpivot columns" and "Merge Querys" (Left Join) in Query Editor. This solves the Problem and is much faster but not as comfortable als the custom function.

     

    Best Regards.

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi Salfii,

 

Instead of do it with Power Query(M) in Query Editor, have you tried doing it in Data View by creating a relationship between the "StringMap" table and the fact table, and then use RELATED function(DAX) to create a new calculate column the fact table to get the related EntityName, or just using LOOKUPVALUE fucntion(DAX) without creating any relationships to see if there is any performance improvement in your scenario? :smileyhappy:

 

Regards

  • Salfii's avatar
    Salfii
    Advocate IV
    8 years ago

    Hi v-ljerr-msft,

     

    thanks for the ideas but I want to keep this in Query Editor and do it with M because it´s a modelling-problem.

     

    LOOKUPVALUE looks tempting here, but at least for me, this is only some kind of workaround and might worsen the GUI/DAX-Performance for the enduser, which is more important than the query performance in my eyes. The bad query performance is only anoying at developement time because there is not a high frequecy reload schedule in production.

     

    I´m now using another way which involves "unpivot columns" and "Merge Querys" (Left Join) in Query Editor. This solves the Problem and is much faster but not as comfortable als the custom function.

     

    Best Regards.