CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-custom-visuals
TestMcTesterson
TestMcTesterson

Reputation: 133

PowerBI - one filter for multiple datasets

Full disclosure, I am a powerBI n00b. I am working on a report for data generated by an external system. This system exports data into different tabs in an excel sheet(lame, I know). I am needing to get a single filter to filter data from all 4 data sets. Let's say this column is "last name". I have tried to create these relationships, and it seems to work....however I am convinced this is not the proper way to handle this.

Cardinality of Many to Many, and a cross filter direction of both for each relationship

Alternatively, I read about creating a new table here, so I also tried this:

Lastname = DISTINCT(
            UNION(
                VALUES('Dataset1'[Lastname]),
                VALUES('Dataset2'[Lastname]),
                VALUES('Dataset3'[Lastname]), 
                VALUES('Dataset4'[Lastname]))
            )
        )

And created similar relationships above, with cardinality of many to many and cross filter direction of both.

Does anyone know of a better way? Thanks in advance(and I apologize for being obtuse)!

Upvotes: 1

Views: 4231

Answers (1)

TheRizza
TheRizza

Reputation: 2052

Power BI works best when the data is in a format called a "Star Schema". Your 4 tabs are likely "Fact tables" and you need a "Dimension table" to do the filtering you need.

So, yes, create the Lastname table. On the link you referenced, yes, there will be a problem setting up the relationships if there is a null Lastname, so if that's a thing in your data, you'll have to filter it out.

When you set up the relationships, they should be One to Many and filter in a single direction. When you use Lastname in your reports or slicers, always use it from the Lastname table, never from the the 4 Fact tables. It is best practice to hide Lastname in those 4 tables from the Model page.

Rarely should you use Many to Many or filtering in Both directions. If you are, it's often a sign that your data is modeled incorrectly. I would ask questions here before using either setting until you are more familiar with data modeling in a Star Schema.

Upvotes: 1

Related Questions