Forum Discussion

ERing's avatar
ERing
Post Partisan
1 year ago
Solved

Is this simple Data Model correct?

I have a report currently set up with the data model below. I have two tables (Table_A, Table_B) and I need my report to show a table visual with measures from both tables.   I usually try to have ...
Need Help
  • sevenhills's avatar
    sevenhills
    1 year ago

    In Power Query, Create query "DIM_STATE" as below:

    let 
    Source = Table_A,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"State"}), 

    Source1 = Table_B,
    #"Removed Other Columns1" = Table.SelectColumns(Source1,{"State"}), 

    SourceCombined = Table.Combine({#"Removed Other Columns", #"Removed Other Columns1"}),
    #"Removed Duplicates" = Table.Distinct(SourceCombined),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [State] <> null and [State] <> "" and [State] <> "null")
in
    #"Filtered Rows"

    Output:

     

     

    ----------------------------------------------------------

    Similarly for "DIM_DEPARTMENT"

    let 
    Source = Table_A,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Department"}), 

    Source1 = Table_B,
    #"Removed Other Columns1" = Table.SelectColumns(Source1,{"Department"}), 

    SourceCombined = Table.Combine({#"Removed Other Columns", #"Removed Other Columns1"}),
    #"Removed Duplicates" = Table.Distinct(SourceCombined),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Department] <> null and [Department] <> "" and [Department] <> "null")
in
    #"Filtered Rows"

     

     

    -----------------------------------------------------------

    Close and apply changes

     

    --------------------------------------------------------

     

    Adjust the data model like below:

     

     

     

     

     

v-dineshya's avatar
v-dineshya
Community Support
1 year ago

Hi ERing ,

Thank you for reaching out to the Microsoft Community Forum.

 

Please follow below steps.

1. Create a Separate Department Dimension Table(Bridge table):
Create a distinct list of Departments and relate it one-to-many to both Table_A and Table_B.

ex:- Table_A to Bridge table(Many-to-one) and Bridge table to Table_B(one-to-Many)

Use this new table for slicers. This avoids the pitfalls of many-to-many and bidirectional filtering.

2. Keep Filtering Direction One-Way Where Possible:

Minimize bidirectional filters only enable when truly necessary. Let slicers flow from the dimension tables into the fact tables.

3. Watch for Performance:
Many-to-many relationships with bidirectional filters can hurt performance at scale. Monitor DAX query times and data model size as you grow.

Note: Your current model works for now and is technically functional. But it's not best practice and could become unreliable or slow with data scale or added complexity. Create a separate Department dimension table and use one-to-many, single-direction filters.

 

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

sevenhills's avatar
sevenhills
Super User
1 year ago

A small correction to your reply. Bridge table is a different concept and wont apply here!  I am not sure, as you replied as ".., Department as Dimension Table (Bridge Table)... "

 

Thank you