Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Expected Results
Data Set
Our PBIX has three simple tables:
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
Course Assignment Table
There are too many Course Assignment rows to show in one visualization snippet, hence two images follow:
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.)
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
Solved! Go to Solution.
@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
The table visual doesn't need any filters.
Proud to be a Super User!
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.
Proud to be a Super User!
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
@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
The table visual doesn't need any filters.
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |