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 a star schema set up with a Fact table and surrounding dimension tables, however that isn't available to me for this report.

Table_A is connected to Table_B using "Department", however this is a Many to Many relationship.

I want to make sure that this model will display the measures correctly with the report slicers from coming from Table_A and measures coming from both Table_A and Table_B.

I've done some testing and so far the measures appear to be displaying correctly, however I want to make sure this model is acceptable and correct.

Am I going to encounter any issues with this?


SAMPLE FILE 




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:

     

     

     

     

     

22 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

    I think, you may need to do in Power Query create two tables (dims):

    • Unique values of Department from both tables
    • Unique values of State from both tables

     

    In the data model use these two to join to the transaction data (aka facts)

     

    In the visuals, use these two table for slicer!

     

    Typical Fact - dim model approach.

     

    Thanks

    • ERing's avatar
      ERing
      Post Partisan
      1 year ago

      Thanks sevenhills 

      Can you provide any guidance on how to create the table of unique values from Department table and the tables of unique values from the State table?

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Right now, your measures may be correct. But the many-to-many relationship between Table_A and Table_B on Department can cause filter confusion, wrong results, or performance issues later as the model grows. To future-proof it, it's better to create a separate Department table and connect both Table_A and Table_B to it with one-to-many relationships.

    • ERing's avatar
      ERing
      Post Partisan
      1 year ago

      Thanks danextian What is the best way for me to create these tables?

      • danextian's avatar
        danextian
        Super User
        1 year ago

        You can create this calculated table:

        Departments =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table_A, Table_A[Department] ),
        SELECTCOLUMNS ( Table_B, Table_B[Department] )
    )
)

         

        In Power Query, you would need to append Tables A and B as a new query, keep only the Department column, and then remove duplicates to create the list.


        The decision between DAX and Power Query depends on the situation. If the transformation required to extract the departments is very complex or if it involves working with a large dataset before reaching the final list, then creating the dimension table using DAX would be the better approach for efficiency. It avoids recalculating or reloading data in Power Query. 


        Note:
        When appending queries as a new query in Power Query, the referenced queries (Tables A and B) are still fully reevaluated during refresh. It's as if the entire logic of both tables is brought inside the new appended query. This means any heavy transformations done earlier on Tables A and B will still impact the refresh time of the appended result. It doesn't isolate or "freeze" the earlier steps. Any changes made to the referenced queries will also cause the append version to re-evaluate.


        For straightforward or lightweight transformations, doing it in Power Query is typically preferable.

    • ERing's avatar
      ERing
      Post Partisan
      1 year ago

      Hi gmsamborn Thansk for you solutions. I see how the DAX example works

      I'd also like to see how the PQ version works, but it looks like there is some issue with the file path.


      Do you know what the error here is?




      • gmsamborn's avatar
        gmsamborn
        Super User
        1 year ago

        Hi ERing 

         

        Sorry about the path names. To be able to do this in Power Query, I first had to export the existing data and then import it into a new copy.

        I made the assumption that all departments and all states would be in 'Table_A'.  If that isn't the case, it's not hard to change.


        In Power Query:
        - Make a reference to your 'Table_A'
        - select the [Department] column
        - remove duplicates
        - rename to DimDepartment


        - Make a reference to your 'Table_A'
        - select the [State] column
        - remove duplicates
        - rename to DimState

         

        Exit Power Query

         

        I hope this makes sense.

         

  • 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

       

  • ERing's avatar
    ERing
    Post Partisan
    1 year ago

    gmsamborn    sevenhills    v-dineshya    danextian 

    I'm still struggling with a solution for this. I used DAX to create tables for DIM_STATE and DIM_DEPARTMENT.

    The real data in my real report has blank values for State and blank values for Department. Thus the DIM tables I'm creating also result in a Blank row. 

    Is there any way I can solve this? Open to doing this in Power Query as well if anyone can provide guidance on how to do so.

    DIM_STATE = DISTINCT(UNION(DISTINCT('Table_A[State]),DISTINCT('Table_B'[State])))

DIM_DEPARTMENT = DISTINCT(UNION(DISTINCT('Table_A[Department),DISTINCT('Table_B'[Department])))



  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

    Power Query: Your attached file is using external files so I cannot provide the exact PQ. You can copy and paste the csv data in the first step of query in PQ and then reattach, either me or someone will help.

    Power Query Approach

    1. Extract Unique Values

      • Reference Table_A, keep only the State column, and remove duplicates.

      • Reference Table_B, keep only the State column, and remove duplicates.

      • Append these two queries and remove duplicates again.

      • Adjust the data type if needed.

      • Name this table Dim_State.

    2. Repeat for Department

      • Follow the same steps for Department to create Dim_Department.

    3. Adjust the Data Model

      • Use Dim_State and Dim_Department as lookup tables.

      • Establish relationships with fact tables.

      • Use them for filtering.

      • sevenhills's avatar
        sevenhills
        Super User
        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:

         

         

         

         

         

  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

    DAX way, I less recommend, the syntax:

    DIM_STATE = 
DISTINCT(
    UNION(
        FILTER(VALUES('Table_A'[State]), 'Table_A'[State] <> BLANK()),
        FILTER(VALUES('Table_B'[State]), 'Table_B'[State] <> BLANK())
    )
)

DIM_DEPARTMENT = 
DISTINCT(
    UNION(
        FILTER(VALUES('Table_A'[Department]), 'Table_A'[Department] <> BLANK()),
        FILTER(VALUES('Table_B'[Department]), 'Table_B'[Department] <> BLANK())
    )
)

     

    To filter multiple conditions, you can do this ... 

    'Table_A'[Department] <> BLANK() && 'Table_A'[Department] <> ""


    FYI: If you dont have blanks, then you can do as:

    DIM_DEPARTMENT =
    DISTINCT(
        UNION(
            Values('Table_A'[Department]),
            Values('Table_B'[Department] )
        )
    )
     

    Hope this helps!

  • sevenhills's avatar
    sevenhills
    Super User
    1 year ago

    Either you use PQ or DAX way, data model should be like:

    User DIM_STATE and DIM_DEPARTMENT for the filters. Hope this helps!
    Thanks