DAX of measure in table/matrix to summarize

[yishenhui]

I'm looking for a DAX code that can summarize a column into a table or matrix visual. It bases on if a string column contains the value of another string column. I prefer it could be dynamic filterred by outside of the table/matrix visual. I created DAX measure like below: (Currentpath is the value that each line would look for; MatchingRows is a temp table that having workitempath contains the CurrentPath; Result is to sumarrize the amount)

Total Completed Work (Self + Descendants) =

        VAR CurrentPath = SELECTEDVALUE(Query1[WorkItemPath])
        VAR MatchingRows =
                FILTER (
                         Query1,
                         Query1[WorkItemPath] = CurrentPath || LEFT(Query1[WorkItemPath], LEN(CurrentPath) + 1) = CurrentPath & "|"
                   )
        VAR Result =
                 SUMX ( MatchingRows, COALESCE ( Query1[CompletedWork], 0 ) )
        RETURN
                 IF ( ISBLANK(CurrentPath), BLANK(), Result )

 I found the result is not expected. looks like it only returns the amount of current row instead of the summary of itself and descendants. Is there any solution to achieve it

[Poojara_D12]

Hi @yishenhui 

You're working with hierarchical data in Power BI, where each row represents a work item, and its position in the hierarchy is identified by a string path column like WorkItemPath. You want to calculate the total "Completed Work" not just for each individual item, but also include the work completed by all its descendants in the hierarchy. To do this, you've written a DAX measure that tries to find all rows where the WorkItemPath starts with the current item's path—indicating it's either the item itself or a descendant. However, the issue you're facing is that the result only shows the value for the current row and not the sum of the descendants. This is because your filtering logic isn't properly overriding the visual context to scan the full dataset. By default, DAX evaluates the measure row by row within the visual, so unless you explicitly tell it to look at the entire table and filter for matching paths, it won’t include descendants. To fix this, the measure should use the CALCULATE function with the ALL function to remove the visual filter on Query1, then apply a custom FILTER that checks if each row's WorkItemPath either equals the current path or starts with the current path plus the delimiter (e.g., "|"). This adjusted measure will correctly find all matching rows—self and descendants—and sum their "Completed Work" values. It also remains dynamic, so it will respect external slicers and filters in the report, giving you the correct aggregated values in your table or matrix visual.

[v-echaithra]

Hi @yishenhui ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @yishenhui ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[techies]

Hi @yishenhui As i understand, does your data contain workitemid and parentid, then you might consider using PATH functions in DAX?

[yishenhui]

Yes, @techies  . The data does contain workitemid and parentid. I tried using PATH but it didn't give the right result. Then I broke it down to steps and using M code and created the path column before it load to visual or DAX. However, it's still not working.

[techies]

ok, please try this measure

Total Completed Work =
VAR CurrentID = MAX(Query1[WorkItemID])
VAR Result =
CALCULATE(
SUM(Query1[CompletedWork]),
FILTER(
ALL(Query1),
PATHCONTAINS(Query1[Path], CurrentID)
)
)
RETURN
IF(ISBLANK(CurrentID), BLANK(), Result)

where path is the calculated column like this- 

Path = PATH(Query1[WorkItemID], Query1[ParentWorkItemID])

[yishenhui]

Hi @techies PATHCONTAINS did the same thing. I tried your solution. Because the table is part of a large table, some ParentWorkItemID are not existed in the column WorkItemID in this table. That could cause error when using PATH to generate the calculated column. I used Power query and generated the PATH with delimiter "|" and replaced with null when the parentID not found. Your codes captures all the value included the rows of workitem and its descendants. But the issue is in the FILTER part, you use ALL function which takes everything regardless the filters out of the table visual, and this is not I want to. I tried remove ALL function, then it did the same thing as other (e.g. LEFT, SEARCH, CONTAINSSTRING...)

[v-echaithra]

Hi @yishenhui ,

Thank you for the update, 

We wanted to kindly follow up to check if the solution provided by @Nasif_Azam  for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[Nasif_Azam]

Hey @yishenhui ,

It seems that the issue with your measure is that the FILTER function is only selecting the rows where the WorkItemPath is equal to the CurrentPath or where it starts with the CurrentPath, but it may not correctly capture the descendants of the CurrentPath for aggregation. To address this, you can modify the logic slightly to include the "descendants" correctly by using the CONTAINSSTRING function to check for substring matches instead of just the LEFT function. This will allow you to capture all rows where WorkItemPath contains the CurrentPath, including any descendants.

Total Completed Work (Self + Descendants) =
    VAR CurrentPath = SELECTEDVALUE(Query1[WorkItemPath])
    VAR MatchingRows =
        FILTER (
            Query1,
            CONTAINSSTRING(Query1[WorkItemPath], CurrentPath) 
        )
    VAR Result =
        SUMX ( MatchingRows, COALESCE ( Query1[CompletedWork], 0 ) )
    RETURN
        IF ( ISBLANK(CurrentPath), BLANK(), Result )

 

Dynamic Filtering:

Since you mentioned you'd like it to be dynamically filtered by outside of the table/matrix visual, the use of SELECTEDVALUE(Query1[WorkItemPath]) will naturally allow this dynamic filtering to happen based on whatever context is applied to the visual.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[yishenhui]

Hi @Nasif_Azam , Thank you.

However, the codes with CONTAINSSTRING does not work. I tried that and also tried SEARCH. I believe the issue maybe on the row-content feature of table/matrix. I did a test. I created another measure with simaliar code but replace currentpath with a specific root workitemid. I added it into the table visual to count and also create a card visual with that measure and count. the count in the table is one and the one in card gives the right result. That means the measure actually captures the data but in the table visual, it stays on the current row even you using the temp table variable expecting to capture all descendants. I'm looking for the solution that would work in the table/matrix visual and dynamic filter (otherwise I could create calculation column)

[Nasif_Azam]

Hey @yishenhui ,

Thank you for your feedback. It seems like the issue is related to the behavior of the table/matrix visual when working with measures. The measure is correctly calculating the sum when used in a card visual, but it doesn't display the expected result in the table visual due to row-level context in the matrix. The key problem here is that the table/matrix visual operates row-by-row, meaning that it evaluates the measure for each row in isolation. So even though you're trying to get the descendants, the row context isn't allowing the measure to behave as you want in the table visual.

Try this version of the DAX:

Total Completed Work (Self + Descendants) =
    VAR CurrentPath = SELECTEDVALUE(Query1[WorkItemPath])
    VAR MatchingRows =
        FILTER (
            ALL(Query1),  -- Remove row context, considering all rows in the table
            CONTAINSSTRING(Query1[WorkItemPath], CurrentPath)
        )
    VAR Result =
        SUMX ( MatchingRows, COALESCE ( Query1[CompletedWork], 0 ) )
    RETURN
        IF ( ISBLANK(CurrentPath), BLANK(), Result )

Dynamic Filtering:

Since you mentioned you'd like it to be dynamically filtered by outside of the table/matrix visual, the use of SELECTEDVALUE(Query1[WorkItemPath]) will naturally allow this dynamic filtering to happen based on whatever context is applied to the visual.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[yishenhui]

Hi @Nasif_Azam ,

Thank you. ALL did remove the row-level context and captured all descendants. However, it also removed the other filter outside the table visual. (I discussed above with techies as well) It's  better but it doesn't meet the requirement for dynamic filter.

[Nasif_Azam]

Hey @yishenhui ,

The issue is maintaining dynamic filter context while capturing descendant rows, which the ALL function disrupts by removing external filters. To fix this, use the REMOVEFILTERS function instead. It removes the filter on the Query1[WorkItemPath] column but retains other external filters, allowing you to capture all descendants without affecting dynamic filtering in the table/matrix visual. Try the updated version of the measure:

Total Completed Work (Self + Descendants) =
    VAR CurrentPath = SELECTEDVALUE(Query1[WorkItemPath])
    VAR MatchingRows =
        FILTER (
            REMOVEFILTERS(Query1[WorkItemPath]),  -- Keeps the outside filters intact
            CONTAINSSTRING(Query1[WorkItemPath], CurrentPath)
        )
    VAR Result =
        SUMX ( MatchingRows, COALESCE(Query1[CompletedWork], 0) )
    RETURN
        IF ( ISBLANK(CurrentPath), BLANK(), Result )

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[yishenhui]

Hi @Nasif_Azam,

I tried but didn't work. REMOVEFILTERS doesn't work here. it does not return a table, only modifies the filter context, while FILTER funtion needs a table expression for the first parameter. I then try using it in the place of filters (e.g. FLITER(Query1,REMOVEFILTERS(Query1[WorkItemPath]...)) then the row-level context filter apply before REMOVEFILTERS.