Lookup whether a hierarchy path contains a parent ID from a related table

[tjessome]

I have a list of parent IDs in a chart of accounts hierarchy that I want to use as a lookup table to filter a measure for the base level (child) accounts that rollup into those parent IDs. What I have attempted to do is use the PATHCONTAINS function in a calculated column within my dimAccount table:

InterestAccount = PATHCONTAINS( dimAccount[Path], RELATED( InterestAccounts[AccountID] ) )

This method only returns TRUE for the specific parent IDs in the "InterestAccounts" table. However, I want this column to return TRUE for all the child accounts that rollup into the parent IDs. I currently have a 1 to 1 bi-directional relationship between my main dimAccount table and the InterestAccounts table:

I then intend to use this calculated column to filter my fact table for these accounts. Filtering the account hierarchy is simple when I only have one parent ID (e.g., "FS008") that I want to filter by as shown below: 

VAR __AccountID = "FS008" // ID for Balance Sheet
VAR Val =
    CALCULATE(
        SUM( fctTrialBalance[Reporting Currency] ),
        FILTER(
            fctTrialBalance,
            fctTrialBalance[Date] = __PeriodEndDate
            &&
            PATHCONTAINS( 
                PATH( dimAccount[AccountID], dimAccount[ParentID] ),
                __AccountID
            )
        )

I want to avoid having to creating a large and / or filter statement within my CALCULATE function for the various Parent IDs that I want to filter for; both because It would be ugly and because the list of Parent IDs that I want to filter for could change over time, so I want to avoid needing to manually update the DAX code for when this list of IDs changes.

Here is my list of parent IDs in the InterestAccounts table:

Here is a list of the base level child accounts that would roll up under FS00471 within my dimAccount table:

Sample Data: Sample data 

Any help would be much appreciated!

[tjessome]

Ok, I found an article with a pattern that enabled me to achieve what I want via a calculated column:

https://p3adaptive.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-table... 

The adapted code is as follows:

InterestAccount = 
IF(
    SUMX( InterestAccounts,
        FIND( 
            InterestAccounts[AccountID],
            dimAccount[Path]
            ,,0
        )
    ) > 0,
    TRUE()
)

This pattern essentially takes the list of parent ID strings from the InterestAccounts table and attempts to find whether the one of these strings exists in the dimAccount[Path] column for each record in dimAccount; returning TRUE when it finds the string. Leveraging this newly calculated column, I can then filter my fact table for the accounts I want as shown in the following measure:

    CALCULATE(
        SUM( fctTrialBalance[Reporting Currency] ),
        FILTER(
            fctTrialBalance,
            fctTrialBalance[Date] = __PeriodEndDate
            &&
            RELATED( dimAccount[InterestAccount] ) = TRUE()
        )   
    )

Works great. However... now I want to see if I can accomplish the same thing by using a measure instead of creating a calculated column. Reason being is that I want to repurpose this logic for other groups of accounts and do not want to create additional calculated columns. My first attempt at this follows:

IsInterestAccount = 
VAR __StringToFind = SELECTEDVALUE( InterestAccounts[AccountID] )
VAR __StringToSearch = SELECTEDVALUE( dimAccount[Path] )
VAR __Result = 
    IF(
        SUMX( InterestAccounts,
            FIND( 
                __StringToFind,
                __StringToSearch
                ,,0
            )
        ) > 0,
        TRUE()
    )
RETURN
    __Result

This does not appear to work. I think I am having an issue with creating the appropriate row context within this measure. Here is a screenshot of a matrix visual with this measure included:

It should return TRUE for any child accounts under FS00471... but it only does this for the blank row below it!

[tjessome · Accepted solution]

I was able to solve my issue.  The reason why my [IsInterestAccount] measure was not working was actually due to the fact that I had that one-to-one relationship between dimAccounts and InterestAccounts. Once I deleted that relationship, the [IsInterestAccount] measure started to work as intended. Not entirely sure how to describe why this behaviour was occurring.  Final working measures are as follows:

IsInterestAccount = 
    IF(
        SUMX( InterestAccounts,
            FIND( 
                InterestAccounts[AccountID],
                SELECTEDVALUE( dimAccount[Path] )
                ,,0
            )
        ) > 0,
        TRUE()
    )

NetInterest_RC = 
VAR AcctShowRow = [BrowseDepth_BPCAcct] <= [RowDepth_BPCAcct]
VAR EntityShowRow = [BrowseDepth_BPCEntity] <= [RowDepth_BPCEntity]
VAR TaxEntityShowRow = [BrowseDepth_TaxEntity] <= [RowDepth_TaxEntity]
VAR AuditIDShowRow = [BrowseDepth_AuditID] <= [RowDepth_AuditID]
VAR ScopeShowRow = [BrowseDepth_Scope] <= [RowDepth_Scope]
// For dates with values in the fct table, calulate the max date to filter the fct table by.
VAR __PeriodEndDate = 
    IF(
        [_ShowValueForDates],
        MAXX( 
            CALCULATETABLE(
                dimTimePeriods,
                dimTimePeriods[DateWithTransactions] = TRUE()
            ),
            dimTimePeriods[Date]
        )
    )
VAR Val =
    CALCULATE(
        SUM( fctTrialBalance[Reporting Currency] ),
        dimTimePeriods[Date] = __PeriodEndDate,
 // This is where I leverage [IsInterestAccount] to filter accounts in dimAccount
        FILTER(
            dimAccount,
            [IsInterestAccount]
        )
    )
VAR Result = 
    IF( AcctShowRow && EntityShowRow && TaxEntityShowRow && AuditIDShowRow && ScopeShowRow, Val)
RETURN 
    Result

This pattern will appropriately flag all child accounts associated with the list of Parent IDs contained in a separate disconnected table (e.g., InterestAccounts). I think this pattern will be very useful for creating custom calculations by grouping nodes in the hierarchy dynamically (based on a list of IDs defined by a user). This also eliminates the need to create calculated columns for each custom grouping required, which I think is more performant.

I recreated this pattern within the Contoso model sample over at daxpatterns.com. Here is the sample pbix file: 21 01 Parent Child - Sample.pbix 

[lbendlin]

Sounds like you want to do a "Filtering up"  pattern. Usually you do that as a two-step process.  Without good sample data it will be hard to advise further.

[tjessome]

Hi Ibendlin, I have added sample data to my original post.