Joining a Spreadsheet to a Semantic Model

[BUC]

Hi all,

I've been asked to set up an MS Form that works in updating a power BI report (won't go into specifics but restaurants need to fill out a form that says how many kilos of food waste there was every day, which can't be held in our main system).  The form literally just asks for the date, location and how many kilos of food was scrapped.

I've set up an MS Form that works via MS Flow to populate a spreadsheet and that bit worksa great.  I've also made the spreadsheet with a VLOOKUP to populate location code so I have a primary key to join it to the semantic model.

I have joined it to the semantic model using a table in the model that deals with locations (called POS Terminal), that bit works fine.  I assumed this connection would be enough to pull in other data, such as number of customers, number of transactions etc but the transaction count is not being split by date, it just gives me a total over and over:

There's no date field in that table to join the date column in my spreadsheet to, so I tried to connect the date to a transaction date table that the location table is also joined to.  But I get an error saying "Table Transaction date filters table POS Terminal, which is from the same power BI Data source or Analysis services data source, through a path that exists outside of the data source."

I can't edit any relationships within the semantic model as it's all locked down; I can only control links between my spreadsheet and the model.  I tried switching the direction of the join but then I get the opposite issue; the transaction count is ok but the weights just revert to the total for each line.  Any ideas?

[v-yohua-msft · Accepted solution]

Hi, @BUC 

Consider using Power Query to merge spreadsheet data with semantic model data, rather than relying solely on direct relationships in the semantic model. Power Query allows for more flexible data manipulation, including merging tables based on common keys, and extending the resulting tables to include the required fields. 

If your semantic model is in Power BI, you can create calculated columns or measures to help aggregate transaction counts by date, even if a direct relationship between dates doesn't exist in the model. DAX is very powerful in creating dynamic aggregations based on relevant data
If you're using Power BI Desktop, a composite model might provide a solution. Composite models allow you to combine direct queries and import sources in the same report, which addresses some of the limitations of not being able to edit existing semantic model relationships.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.