cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dclmorr
Frequent Visitor

Paginated Report Build using PowerBI Dataset

I have a Paginated Report setup that has a PowerBI DataSet as my DataSource.
That PowerBI Dataset has a Products table and a Regions table, with a 2-way (Cross-filter = Both) 
relationship between the tables.  When I review in this Dataset in the PowerBI Desktop, I see the 
2-way Relationship working, so when a Filter is applied to Products, it limits the Regions values.

 

But in the Paginated Report, where I have a Dataset created using the Query Designer that uses the
Product field (From Products table) and the Region field (from the Regions table), and I apply
a Filter to the Product (Setting the Dimension/Heirarchy/Operation/FilterExpression) at the top
of the Query Designer, I see my Products values filtered but its still using all Regions. It looks
like a full outer join result.

 

So I was originally assuming that the relationships in the PowerBI dataset would apply in Pag. Reports,
but its not looking like this is true. And I'm also have not been able to find any discussion or doc
on this.

 

Can anyone confirm that the relationships do not work in Pag. Reports?
Or have I set something up incorrectly and they really do work?
Any workarounds others are using?

 

Any feedback is of course appreciated. Thanks in advance.

1 ACCEPTED SOLUTION
dclmorr
Frequent Visitor

I did open Ticket with Microsoft and did get an answer from them.
The PowerBI Dataset Relationships are not supported when accessed from 
Paginated Report Builder.  There are two suggested ways to work around these 
are to either rely completly on Measures from a PowerBI DataSet, or do setup 
a Derived table by creating new Table using a DAX table funcion like FILTER. 
And then relying on this single table for the Pag.Report.   In my case, I needed both 
of these ideas.  I created a Drived table using FILTER to limit my main Fact table as needed.

Then I can to create the various Measures needed in this FILTERED table to be used by the report.

It works, but looking forward to the future when this can be done much easier.

View solution in original post

9 REPLIES 9
jahemmy
Frequent Visitor

Any Update on this as its been about a year since the last comment here?

chrisbrigg
Frequent Visitor

This still seems to be an issue as of 4/26/21.

v-diye-msft
Community Support
Community Support

Hi @dclmorr 

 

Sorry I can't repro your issue on my side, please kindly raise it to support team for better advice. thanks!:

https://powerbi.microsoft.com/en-us/support/ 

Support Ticket.gif

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Thanks for the response @v-diye-msft .   I've made a small Repro case and put the files on a OneDrive share.  

OneDrive Link to Repro files 

 

For the PBIX, you will see its Filtered to the BusinessUnit "Energy - Package".  And has a filter on Location "A1*".
Because of the BusinessUnit filter, the only value shown is "A142".

DataSet_LeeRepro1 - Power BI Desktop.png

 

And then in the RDL, if you'll open the QueryDesigner you see a BusinessUnit filter set to "Energy - Package" just 
like the PBIX.  And there is a Parameter added to the report for LocationName.  If you run the report, you can 
select the LocationName parameter list and you'll see a large number of "A1*" values to select from, so it is 
NOT limited to just "A142" like the PBIX implementation is.  So I'm assuming this means that the Relationship 
defined in the PBIX is not being used in the RDL that references this published PowerBI Dataset.

QueryDesigner_LeeRepro1_.rdl - Power BI Report Builder.png

 

Report_LeeRepro1.rdl - Power BI Report Builder.png

I have to guess this is just a basic misunderstanding on my part as to how an RDL dataset works when using the 
PowerBI published data as the DataSource but I'd like to understand why.  Or of course if I'm just doing something 
incorrectly and it can work that would be great.  

dclmorr
Frequent Visitor

I did open Ticket with Microsoft and did get an answer from them.
The PowerBI Dataset Relationships are not supported when accessed from 
Paginated Report Builder.  There are two suggested ways to work around these 
are to either rely completly on Measures from a PowerBI DataSet, or do setup 
a Derived table by creating new Table using a DAX table funcion like FILTER. 
And then relying on this single table for the Pag.Report.   In my case, I needed both 
of these ideas.  I created a Drived table using FILTER to limit my main Fact table as needed.

Then I can to create the various Measures needed in this FILTERED table to be used by the report.

It works, but looking forward to the future when this can be done much easier.

I faced the same issue, as I make a heavy use of dimension tables in PowerBI Desktop. At first, I thought that PowerBI Report Builder would work with the relations between the tables, but I saw that it is not the case thanks to this thread (after weeks of wondering what I was doing wrong).

The solutions proposed here work, but I find this to be an ugly workaround.

 

Is there any plan from Microsoft to actually support the relationships when connecting PowerBI Report Builder to a PowerBI data source? That would be a HUGE step forward and would totally make sense as those 2 tools should be as integrated as possible.

dclmorr
Frequent Visitor

I agree @gnumarco , I would really like to see better integration from Microsoft also.  For other Paginated reports I've had to make I have had to forsake any PowerBI integration and I'm back to just using Paginated reports via direct SQL queries just like it was a SQL Report server report.  With all the progress that PowerBI provides, to have to resort to the very old style reports for Paginated reports seems very strange.

Indeed @dclmorr . And I would use SQL queries on a SQL database if my data were on a SQL database...but my data is in MongoDB...I use the ODBC BI Connector to pull the data to PowerBI desktop, and PowerBI Report Builder does not suport ODBC (and anyway, relationships would be inexistant).

What I am really wonderring is why wasn't this almost the first thought when Microsoft integrated PowerBI Datasets support in PowerBI Report Builder? That is a huuuuge pain in the ... not having the relationships.

dclmorr
Frequent Visitor

I've just signed up for the Microsoft Business App virtual conf in May.  Maybe that will provide an opportunity to ask that question.  I'm pretty sure there will not be a session on Paginated-Reports, I'm guessing Microsoft if not trying to put it in the spot light given its current state.  

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors