topic Re: Can't filter a calculated table to get children from selected in DAX Commands and Tips

Can't filter a calculated table to get children from selected

RENEALVAREZM — Mon, 07 Oct 2024 18:38:51 GMT

Hello everyone and apologies if this has been answered before. I cannot find any answer.

This is my scenario:

Plans table contains the following important columns for the scenario: [Plan] (the name of the plan), [PlanType] (the type of plan, which can be Portfolio, Program, Project, Epic, or Standalone Project), [Id] (the unique identifier of the plan), [Parent] (the identifier of the parent plan), [Plan Path] (a calculated column using the PATH function to show the access path of a plan in the parent/child relationship), [Plan Level] (the level of the plan in the path using the PATHLENGTH function), [Level 1] (a calculated column using the PATHITEM function corresponding to the top level of the plan hierarchy), [Level 2] (a calculated column using the PATHITEM function corresponding to the Portfolio level), [Level 3] (a calculated column using the PATHITEM function corresponding to the Program or Standalone Project level), [Level 4] (a calculated column using the PATHITEM function corresponding to the Project or Epic level).

What I need to do is to be able to select a value from that table, and then calculate the child plans when selecting a Portfolio or Program.

I have tried different calculated tables.

This one gives me an empty table:

Child Plans =
VAR _selectdplan = SELECTEDVALUE(Plans[Id])

RETURN
FILTER(
ALL(Plans),
PATHCONTAINS(Plans[Plan Path],_selectdplan)
)

This one gives me the parent instead of children:

ChildrenPlans =
VAR plan = SELECTEDVALUE(Plans[Id])
RETURN
CALCULATETABLE(
    Plans,
    Plans[Parent] = plan
)

If I hardcode one value to test like this, it gives me the rows I need:
ChildrenPlans =

VAR plan = SELECTEDVALUE(Plans[Id])

RETURN

CALCULATETABLE( Plans, Plans[Parent] = "f8e991a3-74b9-4d43-b8c8-80dbaee72ea0" )

I'm obviously doing something wrong but can't figure out why.

Any help is appreciated.

Re: Can't filter a calculated table to get children from selected

HotChilli — Mon, 07 Oct 2024 21:20:23 GMT

"What I need to do is to be able to select a value from that table, and then calculate the child plans when selecting a Portfolio or Program." - How are you selecting these values? If these values are from slicers or visuals, you're on the wrong track because you're creating tables with FILTER and CALCULATETABLE (These are created at refresh time and don't respond to slicers or visuals)

Re: Can't filter a calculated table to get children from selected

RENEALVAREZM — Tue, 08 Oct 2024 06:09:56 GMT

Thanks for your prompt response. I'm applying drill-through. This should update the results of DAX expressions correct?
I'm testing with a direct filter in the page, and even if I have the correct Id for the parent, the ChildrenPlans table is not being correctly filtered and I have no idea why.

Re: Can't filter a calculated table to get children from selected

Anonymous — Mon, 28 Oct 2024 09:30:32 GMT

Hi RENEALVAREZM ,

Here I agree with HotChilli. According to your statement, I think you are creating a calculated table.

As far as I know, the calculated table or calculated column will not be dynamic based on slicer.

Here you use max function in your calculated table code, so it will always return the value with max id.

I suggest you to try measure. Measure will return aggregation value which could be dynamic based on slicer.

For reference: Calculated Columns and Measures in DAX - SQLBI

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.