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

[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...)

[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