Forum Discussion

DataVitalizer's avatar
DataVitalizer
Icon for Super User rankSuper User
4 years ago
Solved

Paginated reports / Filtering one dataset by another one

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 i...
Need Help
  • JGroothedde's avatar
    JGroothedde
    4 years ago

    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's avatar
DataVitalizer
Icon for Super User rankSuper User
4 years ago

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's avatar
sevenhills
Icon for Super User rankSuper User
4 years ago

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-34a39c9f6fe7

 

Please share your code removing sensitivie info.