Forum Discussion

davo15's avatar
davo15
Copper Contributor
May 27, 2023
Solved

Using data model and power pivots to link tables in Excel

I have three tabs, each with a simple one column table, named respectively, Models, Options, Components. The rows in each table consist of the unique names of the product models, options and componen...
excel
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    May 27, 2023

    davo15 

    In any case we need to add some aggregation to PivotTable, otherwise that always will be full join. Let do for number of components. Relationships could be as

    and for models <-> options we may use virtual relationship

    # of Components:=CALCULATE (
    COUNTROWS ( options_components ),
    TREATAS ( VALUES ( models_options[options] ), Options[options] )
)

    Result is

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 27, 2023

davo15 

In any case we need to add some aggregation to PivotTable, otherwise that always will be full join. Let do for number of components. Relationships could be as

and for models <-> options we may use virtual relationship

# of Components:=CALCULATE (
    COUNTROWS ( options_components ),
    TREATAS ( VALUES ( models_options[options] ), Options[options] )
)

Result is

davo15's avatar
davo15
Copper Contributor
May 28, 2023

SergeiBaklan 

 

Thank you so much for taking the time to give me such a detailed response. I really appreciate your help.

 

One quick follow-up question. For the models <-> options relationship, is there a reason you opted for a virtual relationship instead of creating the relationship by selecting the common fields in diagram view?

 

Thanks again.

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 29, 2023

    davo15 

    From Models you may filter options in models_options, but from here we can filter options in Options. Filter-up from many-to-one doesn't work.

    For that we need to have bi-directional relationship which is supported in Power BI Desktop, but not in Excel.

    Alternatively we may use virtual relationships or use CROSSFILTER like

    # of Components CF :=
CALCULATE (
    COUNTROWS ( options_components ),
    CROSSFILTER ( models_options[options], Options[options], BOTH )
)

    For more details you may google for "dax bi-directional" or so, first I found Many to Many Relationships in DAX Explained - Excelerator BI

Resources