cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jlkrawcyk
Regular Visitor

Power BI combine a unique column in a new table from multiple tables

Hi,

 

I am creating a matrix with multiple measurements from 5 different tables and the data does not seem to tie in correctly. I changed the relationships multiple times, but nothing seems to work.

Example: Specifically looking at this part (PN-514081) and SPAN you can see data only in Jan and Feb.

matrix.JPG

 

 HOWEVER in the Data there are more reporting periods:

 

data.JPG

 

I have created a unique column  "key" like shown above in each of my tables:

 

LT tables.JPG

 

I consider myself a beginner with creating dashboards and my only guess one how to fix this is to create a new table and pull in all the unique key columns into 1 columnm remove any diplicates and link from those? 

I am not even sure that would work. 

 

Does anyone have any ideas?

 

Thank you!

 

Jordan

1 ACCEPTED SOLUTION
edhans
Super User
Super User

You need to show us your model, but I am pretty sure it isn't a Star Schema - 
Microsoft Guidance on Importance of Star Schema

 

What you need is a Date Table - Creating a Dynamic Date Table in Power Query - and those dates are what become the columns in your Matrix. It would be a 1:Many relationship to the date field in your FACT table - the one with all of that data.

 

Every key field you want to report on needs to be from a DIM (Dimension) table - Date, Vendor, Location, Product, etc. Those then all are 1:Many to the various Fact tables you have.

For example, you have Receipts and Open Orders. To look at those by date, your Date table would relate to the Receipt date in the Receipt table, then the ORder date (or ship date or whatever) in the Order table, then you put the Date from the Date table in your visual (or any field in the date table - Month, Quarter, Year, whatever) and then the measures/values from both of those fact tables - Receipt amount, Order Amount. 

 

It is impossible to overstate the importance of Star Schemas in Power BI. I would highly advise a good beginner book on Power BI - like Supercharge Power BI by @MattAllington 

Trust me - 2-3hrs with this book or a similar great resource will save you dozens of hours of frustration.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
jlkrawcyk
Regular Visitor

Thanks Edhans,

 

So far I have created a Master Key using the Union function to combine all similar values between each of the tables.

 

I had to create an additional column in each table, "reporting period" and set it to the beginning of the month. Reporting Period is included in my key as well.

 

The data seems to be coorporating now. Thanks for you help and suggestion. I am going to request that I get this book. 

 

I am pulled into a lot of project with little resources, and am relying to youtube and google in most cases. I really appreciate our guidance!

edhans
Super User
Super User

You need to show us your model, but I am pretty sure it isn't a Star Schema - 
Microsoft Guidance on Importance of Star Schema

 

What you need is a Date Table - Creating a Dynamic Date Table in Power Query - and those dates are what become the columns in your Matrix. It would be a 1:Many relationship to the date field in your FACT table - the one with all of that data.

 

Every key field you want to report on needs to be from a DIM (Dimension) table - Date, Vendor, Location, Product, etc. Those then all are 1:Many to the various Fact tables you have.

For example, you have Receipts and Open Orders. To look at those by date, your Date table would relate to the Receipt date in the Receipt table, then the ORder date (or ship date or whatever) in the Order table, then you put the Date from the Date table in your visual (or any field in the date table - Month, Quarter, Year, whatever) and then the measures/values from both of those fact tables - Receipt amount, Order Amount. 

 

It is impossible to overstate the importance of Star Schemas in Power BI. I would highly advise a good beginner book on Power BI - like Supercharge Power BI by @MattAllington 

Trust me - 2-3hrs with this book or a similar great resource will save you dozens of hours of frustration.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors