Is this simple Data Model correct?

[ERing]

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 

[ShivaPatpi]

Thanks

[sevenhills]

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

[sevenhills]

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]

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.

[ERing]

@sevenhills 

I have copied and pasted data from my CSV into Table_A & Table_B. Can you work with this?

SAMPLE_FILE_WITH_NULLS 

[sevenhills · Accepted solution]

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:

[ERing]

@sevenhills Thanks very much! I was able to replicate this in my file by using the Advanced Editor. 

I just thought of another use case and wondering if you can help here.

I will likely have a situation with my real data where my tables will:
1. Have columns named differently (but they are the same dimension). For example, in Table_A "State" will be called "State", but in Table_B "State" will be called "Location".

2. The "State" column in Table_A may have a different set of states compared to the "Location" column in Table_B. For example, Table_A.State may contain records of (Florida, Georgia, and South Carolina) while Table_B.Location may contain records of (New York, California, Texas).

How would I adjust the Power Query code to account for the dimension columns being named differently in each table?

I assume I would change Source1 to the following?
Source1 = Table_B,
#Removed Other Columns1" = Table.SelectColumns(Source1,{"Location"})

My confusion is how to address this part of the code when the column in Table_B is named "Location" rather than State.
#Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [State]  <> null and [State] <> "" and [State] <> "null")

SAMPLE FILE 

Thanks again for all your help!

[sevenhills]

This concept is called role playing dimensions. Like date can be sales date, order date, purchase date. 

Coming back to your model, PQ: You are in the right track: We will rename and bring it as common column name, this is the simple technique 🙂 

See if this helps!

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

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

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

and in the data model, link this table column to both state and location in respective tables. 

Let us know how it goes ! 

[sevenhills]

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

[ERing]

@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])))

[v-dineshya]

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]

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

[gmsamborn]

Hi @ERing 

Very good advice from @danextian  and @sevenhills .

Here is a DAX version and a Power Query version.

Also, I got a little lazy with the PQ version because I only included the Departments and States from 'Table_A'.  Not tough to do it using 'Table_B' as well.

I would prefer the PQ version.

Data Model Example File - PQ.pbix

Data Model Example File - DAX.pbix

Let me know if you have any questions.

[ERing]

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]

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.

[ERing]

The data I provided used Excel as a data source. The data in my real report uses Snowflake.

I'm unsure how I can create a DIM_Department table and a DIM_State table in Power Query using the tables I have loaded into Power BI from Snowflake.

[sevenhills]

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]

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]

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]

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