Forum Discussion
How to build a perfect relationship in power pivot that can be used for power bi
Hi,
I have below data in excel and I want to build a perfect relationship between these tables.
https://1drv.ms/x/s!AhUFhM5jR1jqigVjBusT1kna8fyd?e=01qh6U
I will replicate these same relationship for my power bi reports. Currently I am facing issues in power bi data modeling with this data. There are many inactive relationships as shown below.
Please let me know if you need further information
Thanks in advance!
13 Replies
There is no data model in your sample file, only sources. How to transform sources and which relationships to use depends on logic you'd like to have.
we need a way to be able to see a table of all queries together, we then need to be able to filter by client, batch, contact, point, invoice etc and consider all relevant queries.
a good example of this is if i select client i don't just want to see the queries that have been raised at a client level i want to see all queries for that client, so this will include, client, sites, points, batch, invoice.
likewise if i select a site i want to see queries for the site, any points in that site and any invoices / batches for that site. at the moment the ids are separate, we need the modelling to consider the relationships between these queries
Thanks in advance!
I loaded the file into Power BI Desktop and with some minor adjustment see no inactive relationships. To check how good they are it's necessary to understand logic of the data and which measures will be used. Not what I know.
To build similar relationships in Power Pivot shall be not a big job if know exact logic. Before that you need to massage source data by Power Query and load result for each table into the data model. Perhaps initially your sources are in some database, when you may work directly with it without loading data into the grid.
Here is the file.
