Forum Discussion
Advocate IIUse existing Power BI Dataset (with relationships) in Report Builder
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!
Advocate IIThanks 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!
Advocate IVHi 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-Performance/m-p/2228579#M22119. Thanks to him I could improve my code and include a multi-valued parameter.
Hope it helps.