Forum Discussion
Calculating Total Expense based on page filters when user selects parent from list of dim members
Hey Liu,
I wanted to thank you again for your time. I was able to figure out a solution that worked for my use case. It may not be pretty or efficient but it works and I will use until I can find a better solution. If you have any suggestions to improve, please let me know.
To summarize, I created a second department calculated table, with no relationships, and used the selectcolumns function to bring in columns from the original deptdimension table. I then used this table as a filter in my URL and used the members from this "disconnected" table to get a selected value. I then used this selected value to determine if it exists in path column from my original deptdimension table. If so, I encorporated your "flag" suggestion and show members if the selectedvalue is in path from my deptdimension table.
I'm new to the community and couldnt find a way to attach my sample PBI. If there is way to do so, please let me know. I can upload file in case helpful to anyone that may be struggling with same scenario. Thanks again for the help!
Best regards,
Jose
Hi jx85854 ,
Here are the steps you can follow：
1. Create measure.
Flag =
var _select=SELECTEDVALUE('DeptDimension'[Key])
var _path=MAXX(FILTER(ALL('DeptDimension'),'DeptDimension'[Key]=_select),[Path])
return
IF(
CONTAINSSTRING(
_path,MAX('Data'[Department]))=TRUE(),1,0)2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hey Liu,
Thank you for taking the time to look at my question. I reviewed the model and it didn't quite work as I envisioned for my report. I didnt do a good job of communicating what I was wanting to see so I've added more details below and I hope that this helps clarify what I'm hoping to accomplish.
1. The URL will be filtering data table by user selection from FullDeptName.
2. If I select a department that is a base level member, the table should only show data related to that member. In my sample data, departments 1, 2, 3, 4 and 5 are base level members or at level 3, Admin and Supply are level 2 members. Supply is parent of 1 and 2 and Admin is parent of 3, 4, and 5. Finally, Corp is level 1 member or at top level. Corp is parent of Admin and Supply. In image below, the selection of department 1 on table on left should only show row for department 1. At the moment, selection of department 1, yields rows for department 1, 2 and 3.
3. Selection of Admin department on table on left, should show rows for departments 3, 4 and 5 on table. Departments 3, 4 and 5 are children of Admin. At the moment, selection of Admin department shows departments 1 and 5, which are not its children.
4. Selection of Corp department on left should show rorws for departments 1, 2, 3, 4 and 5 because that is parent of Admin and Supply which are parents of all departments. At the moment, selection of Corp department only shows department 1 on the data table.
5. Finally, I have a relationship between the following: Data/Department and DeptDimension/Depart columns. I'd like to keep this relationship. When I enabled on the supplied model, everything seemed to stop working.
Thanks again. Appreciate your time and help.
Hey Liu,
I wanted to thank you again for your time. I was able to figure out a solution that worked for my use case. It may not be pretty or efficient but it works and I will use until I can find a better solution. If you have any suggestions to improve, please let me know.
To summarize, I created a second department calculated table, with no relationships, and used the selectcolumns function to bring in columns from the original deptdimension table. I then used this table as a filter in my URL and used the members from this "disconnected" table to get a selected value. I then used this selected value to determine if it exists in path column from my original deptdimension table. If so, I encorporated your "flag" suggestion and show members if the selectedvalue is in path from my deptdimension table.
I'm new to the community and couldnt find a way to attach my sample PBI. If there is way to do so, please let me know. I can upload file in case helpful to anyone that may be struggling with same scenario. Thanks again for the help!
Best regards,
Jose