Forum Discussion
Relationships in PBI Publisher for Excel
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
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
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?
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:
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
