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

     

     

sevenhills's avatar
sevenhills
Icon for Super User rankSuper User
4 years ago

 

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'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.

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      4 years ago

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

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

        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. 

    • 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.