cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
T2
Advocate I
Advocate I

Filtering a summary table based on a SELECTEDVALUE()

UPDATE: Link to sample pbix

 

I'm stumped. I have reviewed many third-party sources to no avail. I may very well be missing something obvious.

At the end of this post is a section headed References. It shows the most promising solutions to this problem which, while close, alas, do not work for my use case. I can only find solutions for selecting subsets of a table in which the column to be matched is included in the table visualization. The solution I seek is a summarization of values in a branch of the PATH hierarchy.

 

Desired Behavior

  1. A user will select an employee from a slicer.
  2. Using the Employee ID in the row selected by the slicer we will filter the rows in the Course Assignment table.
  3. A table visualization, Course Summary, only reflects the Course Assignments for whom the Employee ID selected in a slicer is found in the PATH() for that course assignment’s rows. That is, only show course assignments for those employees in the selected employee’s branch of the employee hierarchy.

 

Expected Results

  • Given the data shown in the Data set section, below, when the user selects Employee ID 108, three rows will be shown in the Course Summary visualization, those for Employees 108 and 110.

T2_0-1618250302732.png

 

  • When the user selects Employee ID 103, three rows will be seen in the Course Summary visualization, those for Employees 103, 104, and 106. (Employee 112 is in the selected branch of the hierarchy but has no course assignments.)

T2_1-1618250312660.png

 

Data Set

Our PBIX has three simple tables:

 

T2_0-1618250466414.png

 

  • The Employee table has a custom column, EmpPath, whose value is generated with PATH()
  • The Course Assignment table has the state of each employee/course pair, 1 = True
  • The Selected Branch table is a subset of the Employee table solely used for the slicer
    • I used this separate table rather than refer back to Employees to ensure no side effects would creep in.

There are several measures such as these in the tables, the formulations having been referenced in other solutions or are our own variants. None do what we need, which is to dynamically set the value for each row based on the selected employee when the visualization does not contain the matching field (e.g., EmpPath) in the visualization. 

 

‘Employee’[Employee is in Selected Branch] =

if(PATHCONTAINS(SELECTEDVALUE(Employee[EmpPath]),SELECTEDVALUE('Selected Branch'[Employee ID])),1,0)

 

‘Course Assignment’[Course is in Selected Branch] =

if(PATHCONTAINS(calculate(min('Course Assignment'[EmpPath])),SELECTEDVALUE('Selected Branch'[Employee ID])),1,0)

 

 

Considerations

Relationships cannot be used because Power BI does not support functional relationships. That is I cannot create a one-to-many relationship between ‘Employee’[Employee ID] to all rows where it is a subset of the ‘Course Assignment’[EmpPath].

At one time I added a relationship between Employee and Course Assignment, which would allow me to use the Employee table to filter the Courses using [Employee is in Selected Branch]. This did not work.

 

Table Contents

The sample PBIX, link provided at the end of this post, contains these rows:

 

Employee Table

 

T2_1-1618250712324.png

Course Assignment Table

There are too many Course Assignment rows to show in one visualization snippet, hence two images follow:

T2_2-1618250758589.png

T2_0-1618251016999.png

This is a visualization of the hierarchy, not in the PBIX. This is from the PowerPoint hierarchy builder, which I used to create a non-trivial hierarchy in case the edge cases would clue me into the problem. (They did not.)

T2_0-1618250067345.png

 

 

Community Solutions Attempted

The following solutions seemed promising but did not work when used to filter a visualization where we are not merely showing matching rows.

It seems these solutions work as long as the EmpPath is displayed in the visualization which cannot be done if we’re trying to aggregate the counts at the Course ID level.


Link to the Sample PBIX

https://github.com/t2Microsoft/PowerBI/blob/master/Course%20Stats%20using%20PATH.pbix

1 ACCEPTED SOLUTION
DataInsights
Super User II
Super User II

@T2,

 

Try these measures:

 

Is Scheduled Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Scheduled] ), vTable )
RETURN
    vResult

Is Completed Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Completed] ), vTable )
RETURN
    vResult

Is Late Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Late] ), vTable )
RETURN
    vResult

 

DataInsights_0-1618335277312.png

 

DataInsights_1-1618335287882.png

 The table visual doesn't need any filters.

View solution in original post

4 REPLIES 4
T2
Advocate I
Advocate I

This is great! Your solution solves my problem and in a manner very different from what I was attempting.
I am sure I can use this approach for handling other issues as well.  I've had to turn to SQL many times because it wasn't clear to me how I could do it with DAX.

Thank you very much.

@T2,

 

Glad to hear that solves the problem. That's good design to create a clone of the Employee table (Selected Branch) for use in the slicer. This way you don't disrupt your data model by removing the relationship between Employee and Course Assignment.

 

Note to the community: This is an example of a well-written post. The author states the issue and desired behavior clearly, lists attempted steps to solve it, provides screenshots of expected results, and provides a link to the pbix.

Thank you, again, for taking the time to help us out. I also appreciate your positive feedback, which hastens me to remind folks about @Greg_Deckler 's very useful post : How to Get Your Question Answered Quickly 

DataInsights
Super User II
Super User II

@T2,

 

Try these measures:

 

Is Scheduled Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Scheduled] ), vTable )
RETURN
    vResult

Is Completed Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Completed] ), vTable )
RETURN
    vResult

Is Late Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Late] ), vTable )
RETURN
    vResult

 

DataInsights_0-1618335277312.png

 

DataInsights_1-1618335287882.png

 The table visual doesn't need any filters.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors