Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Dynamics 365 CRM, Power BI & Duplicate Values

  • 1.  Dynamics 365 CRM, Power BI & Duplicate Values

    SILVER CONTRIBUTOR
    Posted 14 days ago
    I've connected my Dynamics 365 (online) instance to Power BI, but due to the way values are stored it isn't overly useful out the gate. I've tried the PowerBI Option Set assistance from XRM Toolbox, but that didn't work out like I was expecting. Almost all of my option set values are duplicates across entities.

    DtRY0jJNSUWa7bpFwRhg_DuplicateValues.PNG

    Is this the expected behavior? I guess I (erroneously) assumed that the default values were going to increment correctly across different entities (like a GUID would do).

    Any good way to fix this going forward?

    ------------------------------
    Guy Parisi
    Business Analyst & Quality Manager
    Sprague Pest Solutions
    Tacoma WA
    ------------------------------


  • 2.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    SILVER CONTRIBUTOR
    Posted 13 days ago
    Hi Guy, I believe it will depend how you've set up your option sets in the entities. But looking at the screen shot you've provided, I don't see any duplicates. And by duplicates I mean the same gap value repeated with the same optionsetschemaname and gapentity name. I looks like each line is unique. Please do correct me if I'm wrong about this. What you would want to do is to filter down in your query editor in Power BI so that your optionset table only contains a single optionsetschemaname. Which would essentially mean that from a table connection perspective you would have
    - 1 optionset table for statuscode
    - 1 optionset table for guy_CancellationType
    - 1 optionset table for  guy_DocumentType
    -1 optionset table for guy_BusinessUnit
    -1 optionset table for CancellationReason

    From there you can link these optionset tables to the corresponding fields in the table(s) which would then allow you to report on each optionset individually. I found the same thing when working with optionsets and most of my models contain multiple option set tables filtered at the query level.

    Please let me know if this makes sense.

    ------------------------------
    Daniel Demers
    Chief Digital Information Officer
    Richard Jay Laundry
    ------------------------------



  • 3.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    GOLD CONTRIBUTOR
    Posted 13 days ago
    Guy,

    The trick is to create a lookup table per column and then another lookup to that table to get the value into the relevant table.

    LUT Lead_Status = CALCULATETABLE(gap_powerbioptionsetrefs, FILTER(gap_powerbioptionsetrefs, gap_powerbioptionsetrefs[gap_optionsetschemaname]="statecode"  && gap_powerbioptionsetrefs[gap_entityname]="lead"))

    And in the lead table
    Lead Status = LOOKUPVALUE('LUT Lead_Status'[gap_label], 'LUT Lead_Status'[gap_value], 'Leads'[statecode])



    ------------------------------
    Donal McCarthy
    BrightWork
    Galway
    ------------------------------



  • 4.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    GOLD CONTRIBUTOR
    Posted 13 days ago
    Edited by Ryan Perry 13 days ago
    Hi Guy,

    To beat the proverbial dead horse that has been let out of the gate, yes, you need to filter the master table for each related field. Here's a sample from my model.  I have about a dozen of these, with each one specifying different gap_entityname, gap_optionsetschemaname, and using different renames for the gap_value and gap_label columns.

    let
        Source = DataSourceConnection,
        gap_powerbioptionsetrefs_table = Source{[Name="gap_powerbioptionsetrefs",Signature="table"]}[Data],
        #"Filtered Rows" = Table.SelectRows(gap_powerbioptionsetrefs_table, each ([gap_entityname] = "Lead") and ([gap_optionsetschemaname] = "StatusCode")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"gap_value", "gap_label"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"gap_value", "Value"}, {"gap_label", "Lead Stage"}})
    in
        #"Renamed Columns"

    Good luck.

    ------------------------------
    Ryan Perry
    Business Systems Analyst
    Auric Solar
    ------------------------------



  • 5.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    SILVER CONTRIBUTOR
    Posted 13 days ago
    Hey Guy

    You don't even need to work with the option set assistant. If you use the FetchXML in your Power Query, instead of the OData that you are using, the option set labels are automatically included in your data set, plus some other benefits.

    There's a tool in the XrmToolBox called Power Query Builder that can generate the queries for you. There's a video on my site describing it here.
    https://crmchartguy.com/power-query-builder/

    Cheers,

    ------------------------------
    Ulrik Carlsson
    Microsoft MVP (CRM Chart Guy)
    eLogic
    Petaluma CA
    ------------------------------



  • 6.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    GOLD CONTRIBUTOR
    Posted 13 days ago
    Ulrik,

    This is good to know.  Our quries all use Odata.  If using FetchXML, do you lose query folding?  Not having to transform each options set manually sounds like a nice perk. What are these other advantages (and disadvantages) of which you speak?

    Ryan.

    ------------------------------
    Ryan Perry
    Business Systems Analyst
    Auric Solar
    ------------------------------



  • 7.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    SILVER CONTRIBUTOR
    Posted 13 days ago
    You keep query folding and you have more options when using FetchXML. I.e. you can query a table based on parameters on another table. You also get more details on lookup fields, incl. the name. Generally it works faster too. Especially for advanced queries. Main disadvantage is that the query initially is more challenging, but that's where the Power Query Builder in the XrmToolBox comes in handy.

    ------------------------------
    Ulrik Carlsson
    Microsoft MVP (CRM Chart Guy)
    eLogic
    Petaluma CA
    ------------------------------



  • 8.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    SILVER CONTRIBUTOR
    Posted 13 days ago
    Thanks for sharing Ulrik. Just curious, being FetchXML, is there any limits with the amount of records that will be returned?

    ------------------------------
    Daniel Demers
    Chief Digital Information Officer
    Richard Jay Laundry
    ------------------------------



  • 9.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    SILVER CONTRIBUTOR
    Posted 13 days ago
    Not really. Its paginated, so it is grapping 5k at a time. If you get into a million rows or more you may want to look at the Data Export Service regardless.

    ------------------------------
    Ulrik Carlsson
    Microsoft MVP (CRM Chart Guy)
    eLogic
    Petaluma CA
    ------------------------------



  • 10.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    GOLD CONTRIBUTOR
    Posted 12 days ago
    @Ulrik Carlsson
    Thanks for that - Friday morning made - not sure how I missed the existence of your tool until now!



    ------------------------------
    Donal McCarthy
    BrightWork
    Galway
    ------------------------------



  • 11.  RE: Dynamics 365 CRM, Power BI & Duplicate Values

    SILVER CONTRIBUTOR
    Posted 9 days ago
    Holy rusted metal, this tool is awesome! This should satisfy exactly what I was looking for and it has made querying things so much faster than it used to be! (Activity Pointers table, I'm looking at you.)

    ------------------------------
    Guy Parisi
    Business Analyst & Quality Manager
    Sprague Pest Solutions
    Tacoma WA
    ------------------------------