Relationships in PBI Publisher for Excel

[rjhale]

I recently installed the PBI Publisher tool for Excel, and I'm attempting to create a pivot table based on a dataset that exists in the PBI Service.  I use the Connect to Data button and connect to my dataset.  

My dataset has several tables in it, and the tables are all related in one way or another.  When I created the dataset I setup relationships and cardinality which all works well.  The problem that I'm running into with the PBI Publisher tool is that it does not honor those relationships when creating the pivot table.  This means that I can't add fields from different tables in the dataset to the same pivot table. 

I'm wondering if there is a way to define those relationsips in Excel or the PBI Publisher tool?  If not, I think I will have to simply rework my dataset so that I only have one table for each pivot table that I need to create.

Thanks.

[v-qiuyu-msft]

Hi @rjhale,

Based on my research, in the Power BI publisher for Excel, after we connect directly to data in the Power BI service, data model can't be changed, as we open the connection to Power BI datasets directly. We can just analyze data via PivotTables and PivotCharts.

In your scenario, as long as you have created the relationships among those tables, you can visualize data in PivotTable from different tables. You can define the relationships in Power BI desktop then publish to replace dataset in Power BI service.

Best Regards,
QiuyunYu

[rjhale]

I'm not getting the same results when adding fields from more than one table to the pivot table in Excel.  Let me explain a very simple example: 

• I start in Power BI Desktop and enter data in two tables.  One table has fields for fruit IDs and furit names.  The other table has furit IDs and furit description. 
• I create a basic relationship between the tables in Power BI Desktop (joined on fruit IDs), and then I create a pivot table with the fruit name and fruit description included.  The relationship works correctly here, and the descriptions match to the respective fruit names. 
• I go to Power BI Service, and again, the relationships are honored and work as expected.  In other words, I can recreate the pivot table that I made in Power BI desktop, and I get the same results. 
• I then switch to trying Power BI Publisher for Excel.  I connect to the recently published fruit example dataset.  Now when I add the furit name and fruit description to the table, I get the following:
• 

As you can see the relationship is no longer honored between the two tables.  I'm seeing the same thing in my other projects as well.  This is the most basic example I could think to create to demonostrate the problem I'm experiencing.  I should also note in the example above I did not enter description for Raspberry or Orange.

Any idea why the relationships would work in Power BI Desktop and the Service but not work in PBI Publisher for Excel?

[rjhale]

I realize that I didn't have my link settings correct in my previous post.  Here is the image of the table I was describing in my last post:

[v-qiuyu-msft · Accepted solution]

Hi @rjhale,

From this article: Connect to data in Power BI

Dataset must have measures - The dataset must have model measures defined in order for Excel to treat the measures as values in PivotTables, and to correctly analyze the data.

In your scenario, you must create a measure in Power BI desktop, then place the measure in Values of PivotTable. You can download attached pbix file and test in your environment.

By the way, without getting data from the Power BI dataset, just create a normal PowerPivot table in Excel, if no measure place in PivotTable Values, data cann't be displayed proper based on relationships as well.

Best Regards,
QiuyunYu

[rjhale]

That works much better once I added the measure to the table.  Thanks for the help.