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

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!