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:

     

     

     

     

     

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

        Once all this is done and no errors, go to the "Report View" and use the Slicer values from Dim Department and Dim State.

         

        Note: if you want "null" as output, adjust the pq query above by removing the clause!

         

        Thanks