Forum Discussion
Power BI combine a unique column in a new table from multiple tables
You need to show us your model, but I am pretty sure it isn't a Star Schema -
Microsoft Guidance on Importance of Star SchemaWhat 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.
Community ChampionYou 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.