0

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.

  • Dataset1.Lastname --> Dataset2.Lastname
  • Dataset2.Lastname --> Dataset3.Lastname
  • Dataset3.Lastname --> Dataset4.Lastname

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)!

TestMcTesterson
  • 123
  • 5

1 Answers1

1

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.

TheRizza
  • 1,577
  • 1
  • 10
  • 23
  • Thanks a ton! I am researching now. It seems that I need to go FACT * --> 1 DIM. Where this is breaking down a bit, is that more or less, these different datasets all have the same columns. Basically the same data based on different dates across the 4 data sets. Does the star schema still apply? – TestMcTesterson Nov 24 '21 at 15:19
  • Made some changes, and here is what I did. Still needed to do this to populate the fact table.: Lastname = DISTINCT( UNION( VALUES('Dataset1'[Lastname]), VALUES('Dataset2'[Lastname]), VALUES('Dataset3'[Lastname]), VALUES('Dataset4'[Lastname])) ) ) then I did: Dataset1.Lastname:many --> 1:fact.Lastname Dataset2.Lastname:many --> 1:fact.Lastname Dataset3.Lastname:many --> 1:fact.Lastname Dataset4.Lastname:many --> 1:fact.Lastname – TestMcTesterson Nov 24 '21 at 16:28
  • If the data is the same columns just a different value in the date column, use Power Query and append the 4 files into one table. Your dimension then is a date table. Lots out there on Google on creating a date table and why you want one. Between slicers/filters and time dimension DAX functions, almost every model has at least one date dimension. – TheRizza Nov 29 '21 at 21:06