Forum Discussion
How to build a perfect relationship in power pivot that can be used for power bi
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.
I couldn't open the file you had sent.Can you please attach it in below link if possible?
https://onedrive.live.com/?id=EA584763CE840515%21737&cid=EA584763CE840515
Also, if possible, please share the excel file in which you made minor adjustment.Thanks in advance.
