Paginated reports / Filtering one dataset by another one

[DataVitalizer]

Hi Community,
I have the below power bi dataset, based on which I am trying to created a paginated report.

• The need : My paginated report should visualize columns of T1 where T1.EmployeeID is in T2.EmployeeID where T2.ManagerEmail equals UserID()
• What I have done: I have created a dataset which grabs columns from Table1 then I added T2.ManagerEmail to the same dataset parameter zone as a visible filter

• The issue: When I render the report and use the filter (T2.ManagerEmail) data visulaized (T1 Columns) in my report does not change, am I missing something?

Any suggestion would be appreciated.

Thank you in advance.

[sevenhills]

Can you check these steps and see ... 

• Two datasets, one without parameter and other with parameter. Query in the paginated report for each dataset looks like
  • Say, ManagerEmail's dataset "ds_ManagerEmail"- simple list
    • Query / DAX somewhat look like 
      EVALUATE .. SUMMARIZECOLUMNS ... 
    • You used this dataset for parameter say "Parameter_ManagerEmail", datatype as text, configured available values and default values
  • Say, data to display dataset "ds_Data" - 
    • Query / DAX somewhat look like
      EVALUATE SUMMARIZECOLUMNS ... RSCustomDaxFilter(@ParamName,EqualToCondition,[Power BI Dataset Name For Table 1 Fact].[Field Name],String))
    • In the dataset properties, check the parameters as
      • Parameter Name "ParamName
      • Parameter Value "@Parameter_ManagerEmail", equal expression value is "=Parameters!Parameter_ManagerEmail.Value"

If this looks good, I will try directly evaluating in the query designer, second dataset "ds_Data" with one of the manager email value hard code and see if that filters. 

... let me know if this solves it

Thanks

[DataVitalizer]

Hi @sevenhills 

Sorry for my late reply and thank you for you answer. 

Please correct me if I am wrong, what you mean is filtering the second dataset (ds_data) by whatever email is seleclted from the parameter which is connected to ds_ManagerEmail ?

I am imagining steps this way when thinking sql

ds_Email
select EmployeeID, ManagerID, ManagerEmail from Table1
ds_data: which should be visualized
select EmployeeID, Revenue
from Table2 where Table2.EmployeeID IN (
select EmployeeID
from Table1
Where ManagerEmail = the email selected from the 1st table)

 @amitchandak @AlexisOlson @Greg_Deckler @Fowmy  I am mentioning you here hoping to get suggestions from you if possible. 

Thank you in advance.

[sevenhills]

Theory, you are right. 

With Power BI Dataset, it uses DAX (and not SQL). Tables are queries (in psuedo thinking) 

Try these:

a) To test your DAX working correctly, in the ds_data, go to the parameters tab, hard code some value for the param and see if it works. 

b) with a blank report, go and create dataset in the paginated report using the Power BI dataset connection. In the query designer mode, create one parameter and also the output columns you need. This will create some hidden datasets, which is used for params. 

Some references for you

https://www.bluegranite.com/blog/a-step-by-step-overview-of-paginated-reports-in-power-bi

https://towardsdatascience.com/paginated-report-visual-in-power-bi-everything-you-need-to-know-34a39...

Please share your code removing sensitivie info.

[Greg_Deckler]

@DataVitalizer Are your two tables related to one another in your dataset?

[DataVitalizer]

Hi @Greg_Deckler 

Thank you for your time.

I have a Power BI Dataset based on which I have created two datasets within my paginated report in order to acheive the need, not sure if I must create two datasets!?

Thank you in advance. 

[JGroothedde]

Hi @DataVitalizer ,

Unfortunately the relationships that are present in your Power BI Dataset do not work in the report builder. You can use the LOOKUP function or manually copy the queries from the dataset  into PBI Report Builder as shown in this video.

Cheers.

[Greg_Deckler]

@DataVitalizer No, not two datasets, one dataset with 2 tables but you need to have a relationship between the tables for one to filter the other.

[DataVitalizer]

Hi

@Greg_Deckler  My data model contains two linked tables, I created it on Power BI desktop then publish it to Power BI Service, I am trying to use that published dataset inside power builder

@JGroothedde hope this explains the scenario 

Thank you in advance.

[JGroothedde · Accepted solution]

Hi @DataVitalizer,

In Power BI report builder, try this:

Add a new dataset:

Select your PBI dataset as datasource:

Open the query designer:

In query designer, click the circled icon:

In the text field, paste this query, you may need to edit the table/ field names to fit your situation:

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Table1_Data'[EmployeeID],
      'Table2_Managers'[ManagerEmail],
      'Table2_Managers'[ManagerID],
      "SumValues", CALCULATE(SUM('Table1_Data'[Values]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      501,
      __DS0Core,
      'Table1_Data'[EmployeeID],
      1,
      'Table2_Managers'[ManagerEmail],
      1,
      'Table2_Managers'[ManagerID],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'Table1_Data'[EmployeeID],
  'Table2_Managers'[ManagerEmail],
  'Table2_Managers'[ManagerID]

Press OK, validate the query and press OK again. You should now be able to use the values in the new dataset to achieve your goal.

This query is what Power BI generates when you visualize the desired results in the desktop dataset. To find the query you can use the performance analyzer. That might help you in the future.

You could also use the CALCULATE function to add whichever data you need to your fact table in the desktop version of your dataset so you aren't reliant on relationships. There's a lot of roads that lead to rome in this situation. 

Cheers

[DataVitalizer]

Hi @JGroothedde 

Thank you for your time.

I followed the steps you shared and I got the data to visualize.

When I add a new parameter in the dataset settings and try to render report I get a dependency issue. 

Thank you in advance. 

[JGroothedde]

Hi @DataVitalizer 

Hmm, that's interesting, can you show us what the parameter is set up like?

[DataVitalizer]

Hi @JGroothedde 
I created a paramater based on the resulted dataset ManagerEmail then I added a condition in the filter tab of the dataset where DataSet1.ManagerMail = @ManagerEmail

When rendreding I get this message 

 The report parameter 'ManagerEmail' has a DefaultValue or a ValidValue that depends on the report parameter "ManagerEmail". Forward dependencies are not valid.

Thank you in advance. 

[JGroothedde]

Hi @DataVitalizer ,

Can you try deleting the condition in the filter tab of the dataset and try this:

1) Right-click on parameter and select 'parameter properties'

2) Select 'Available values' in the list on the left

3) Check the 3rd option (Get values from a query)

4) Set the following fields like this:

Does that solve the issue for you?