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

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

     

     

13 Replies

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