Forum Discussion
Calculating Total Expense based on page filters when user selects parent from list of dim members
Hi everyone,
I am trying to use a URL to pass filters to a published Power BI report. The URL works. I can see the page filters on the filter panel and I see data when selecting a department at the "base" level. However, the published report shows up blank when I select a parent. It seems that the report does not recognize the child-parent relationships based on column and table that I am using on the URL to filter for the departments.
I do have a department table set up and have established relationship between this table and the table that holds my data for department. In this department table, I define all department members. I also define the department parents, paths and level of each department. Since the relationship that I established isn't working with page filters, I am now trying to leverage the department table to try to create a calculate formula that will give me sum of dimension member that is selected, even if a parent.
If I'm going about this the wrong way, I would appreciate some help getting pointed in the right direction. I've also included a sample of my data and tables, in case helpful. Thanks in advance for the help.
I am trying to do this via the formula below:
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
3 Replies
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
