Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jx85854
Frequent Visitor

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:

 

Totals =
VAR x =
    SELECTEDVALUE ( DeptDimension[Key] )
RETURN
    CALCULATE (
                SUM ( Data[Amount] ),
                ALLSELECTED ( DeptDimension ),
                PATHCONTAINS ( DeptDimension[Path], x )
            )
 

jx85854_1-1699571566622.png

 

1 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!

 

jx85854_0-1700247890060.png

 

jx85854_3-1700247992403.png

 

 

Best regards,

 

Jose

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1699862434677.png

3. Result:

vyangliumsft_1-1699862434678.png

 

 

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.

 

jx85854_0-1699990144053.png

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.

 

jx85854_1-1699990240766.png

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.

 

jx85854_2-1699990662092.png

 

 

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.

 

jx85854_3-1699990675891.png

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!

 

jx85854_0-1700247890060.png

 

jx85854_3-1700247992403.png

 

 

Best regards,

 

Jose

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.