Calculating Total Expense based on page filters when user selects parent from list of dim members

[jx85854]

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:

Totals =

VAR x =

    SELECTEDVALUE ( DeptDimension[Key] )

RETURN

    CALCULATE (

                SUM ( Data[Amount] ),

                ALLSELECTED ( DeptDimension ),

                PATHCONTAINS ( DeptDimension[Path], x )

            )

[Anonymous]

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

[jx85854]

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.

[jx85854 · Accepted solution]

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