Use existing Power BI Dataset (with relationships) in Report Builder

[rmsolfest]

I created a related set of tables in Power BI for use with Row-Level Security (RLS).  I now want to use the same related dataset in Report Builder for paginated reports and RLS.  In my testing it appears that the relationships are NOT inherently supported within Report Builder. 

I have seen some blogs saying that related tables are not supported in Report Builder and that one needs to create a flat table with all the userids in the fact table as a workaround.

However, I see conflicting information saying that related tables are supported in Report Builder (see below).

Power BI Report Builder - Power BI | Microsoft Docs

Report from a variety of data sources. You can create reports that use relational and multidimensional data from SQL Server and Analysis Services, Oracle, Power BI datasets, and other databases.

Please share your insights in getting a relational Power BI Dataset to work for RLS scenario in a paginated report.  - Thanks in advance!

[rmsolfest · Accepted solution]

Thanks Link - This was a helpful tip to use UserID (similar to USERNAME in Power BI).

SOLUTION DISCOVERED WITHOUT RECREATING A FLATTENED PARALLEL POWER BI DATASET:  Details below...

PROBLEM:  I still don't fully understand the conflicting messages of a using a Power BI dataset with relationships.  Report Builder was not automatically cascading the filter from the parent tables to the child table. 

For example if the Dates[Date] table has a 1 to many relationship Orders[SaleDate], one cannot just bring in columns from both tables and filter on Dates[Date]=03/10/2021.  I was expecting this to only show me the Orders[SalesDate]=03/10/2021.

SOLUTION:  However, I was able to get to a solution by my Report Builder Dataset using DAX SUMMARIZECOLUMNS to effectively create the desired flat data table.  What I realized is that the DAX relationship functions (e.g.: RELATED, RELATEDTABLE) work on the Power BI model to enable one to construct the cascaded filtering that I needed. 

I am now thinking that this is how one "can create reports that use relational and multidimensional data from...Power BI datasets" that is referenced in Report Builder even though it does not automatically cascade filters like in Power BI.

If you know of a simpler or more elegant way of leveraging the Power BI relationships in Report Builder, please share with the community.  Thanks in advance!

[trt18-sistemas]

Hi @rmsolfest,

I was struggling on this problem, because the solution suggested at Microsoft tutorial (copy code from Performance Analyser) only work for small number of rows (lower than 500).
So, I decided to dive deep into this problem and understand how it works. Than I've created my own solution, incluind a way to use parameters (single and multivalues). The only issue this solution doesn't aproach  is RLS, so I can't say if it works but I can't say that it doesn't, either.

Below there is a example code of how to do it:

1. Create table

Instead of use SUMMARYCOLUMNS I used a CREATETABLE function to retreive data from Power BI Dataset:

// DAX Query
DEFINE

VAR tab = CALCULATETABLE(
    SELECTCOLUMNS(
            FACT_TABLE,
        "COL1",
        FACT_TABLE[Col1],
        "COL2",
        RELATED(DIM_TABLE1[COL_A]),
        "COL3",
        RELATED(DIM_TABLE2[COL_B]
    ),
    FACT_TABLE[COL_Y] = @Single_paramater,
    PATHCONTAINS(@Multivalues_parameters, FACT_TABLE[COL_Z])
)

EVALUATE
  Tabela

At the example code above is composed by 3 tables: FACT_TABLE, DIM_TABLE1 and DIM_TABLE2. Where FACT_TABLE is a fact table and the others are dimensional tables. To refer to a column at dimensional tables I used RELATED function.
at Filter parameter of CREATETABLE function, you have 2 options. One for a single valued parameter and another for a multivalued parameter. For the second option, you use PATHCONTAIS function which is equivalent to inString, in other words, it checks if the parameter contais the column value.

For the final,  for a multivalues_parameter to work, you have to transform it to String. So, go to dataset propertis, select Parameters and at multivalues_parameter value, click at expression button (fx). Than input this function: "=join(Parameters!multialues_parameters.value, "|").

That's it!

P.S.1: as explained above, it your dataset needs to retrieve more than 500 rows, it may not work using the code generated at Performance Analyser because it uses the function TOPN(500, ...), limiting the number of rows at 500.

P.S.2: If you'd like to test your code, you can run it at Power BI Desktop. For that, go to Table Tools >> New Table and paste your CREATETABLE code. Don't forget to remove the VAR declaration.

tab = CALCULATETABLE(
    SELECTCOLUMNS(
            FACT_TABLE,
        "COL1",
        FACT_TABLE[Col1],
        "COL2",
        RELATED(DIM_TABLE1[COL_A]),
        "COL3",
        RELATED(DIM_TABLE2[COL_B]
    ),
    FACT_TABLE[COL_Y] = @Single_paramater,
    PATHCONTAINS(@Multivalues_parameters, FACT_TABLE[COL_Z])
)

P.S.3: A big thank you for @loicr for posting his solution at https://community.powerbi.com/t5/Report-Server/Add-parameter-in-query-designer-query-from-Power-BI-P.... Thanks to him I could improve my code and include a multi-valued parameter.

Hope it helps.

[v-xulin-mstf]

Hi @rmsolfest,

When applying row-level security to a Power BI paginated report, you need to assign a parameter to the UserID attribute.

Please refer this.

As a complement, when you connect to power bi dataset from power bi desktop/report builder, the connection mode is set as live connection which means you can't do any modification on the data model(e.g.: table relationship) so you can't see the relationship.

Best Regards,

Link

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