Paginated reports / Filtering one dataset by another one

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