Count records with a specific child at any level in a parent-child table

[Credential]

Hi there,
I would like to determine the number of certain rows in a parent-child table for which a certain child exists at any level. The table has several million data records, which is why a solution with ALL is very slow. Is there a way to only determine the existence of such a child without resetting all filters with ALL or ALLEXCEPT? 

My solution so far:

COUNTROWS(
FILTER(
    ADDCOLUMNS(
        ADDCOLUMNS(
            FILTER(
                'FACT.TJ_Aktiv'
                ,RELATED('DIM.TJ_Aktiv_Flags'[Is_Call])="Y"
            )
            ,"CC DIM_Company_Key"
            ,'FACT.TJ_Aktiv'[DIM_Company_Key]
            ,"CC FACT_PATHLEVEL"
            ,'FACT.TJ_Aktiv'[FACT_PATHLEVEL]
            ,"CC FACT_Aktiv_Key"
            ,'FACT.TJ_Aktiv'[FACT_Aktiv_Key]
            ,"CC FACT_Date"
            ,'FACT.TJ_Aktiv'[FACT_Date]
        )
        ,"CC"
        ,IF(
            NOT ISEMPTY(
                FILTER(
                    ALL('FACT.TJ_Aktiv')
                    ,
                    'FACT.TJ_Aktiv'[DIM_Company_Key] = [CC DIM_Company_Key]
                    && 'FACT.TJ_Aktiv'[FACT_Date] >= [CC FACT_Date]
                    && 'FACT.TJ_Aktiv'[FACT_PATHLEVEL] > [CC FACT_PATHLEVEL]
                    && RELATED('DIM.TJ_Aktiv_LC'[Type]) IN {"A","B","C"}
                    && PATHCONTAINS('FACT.TJ_Aktiv'[FACT_PATH],[CC FACT_Aktiv_Key])
                    --&& 'FACT.TJ_Aktiv'[FACT_Parent_Key]=[CC FACT_Aktiv_Key]
                )
            )
            ,1
            ,BLANK()
        )
    )
    ,ISBLANK([CC])=FALSE
)
)

[Credential · Accepted solution]

I have found a high-performance solution. The model wasn't the problem. It was due to the iterative filtering of the child records with PATHCONTAINS.

Here is my solution that now only takes 0.4 sec instead of 15.0 sec:

COUNTROWS(
	FILTER(
		ADDCOLUMNS(
            SELECTCOLUMNS(
                FILTER(
                    'FACT.TJ_Aktiv'
                    ,RELATED('DIM.TJ_Aktiv_Flags'[Is_Call])="Y"
                )
                ,"CC DIM_Company_Key"
                ,'FACT.TJ_Aktiv'[DIM_Company_Key]
                ,"CC FACT_PATHLEVEL"
                ,'FACT.TJ_Aktiv'[FACT_PATHLEVEL]
                ,"CC PATHLEVEL 1"
                ,'FACT.TJ_Aktiv'[FACT_PATHLEVEL_1]
                ,"CC PATHLEVEL 2"
                ,'FACT.TJ_Aktiv'[FACT_PATHLEVEL_2]
                ,"CC PATHLEVEL 3"
                ,'FACT.TJ_Aktiv'[FACT_PATHLEVEL_3]
                ,"CC PATHLEVEL 4"
                ,'FACT.TJ_Aktiv'[FACT_PATHLEVEL_4]
       		)
       	    ,"CC VM"
        	,SWITCH([CC PATHLEVEL]
    			,1,CALCULATE(NOT ISEMPTY('FACT.TJ_Aktiv'),ALLEXCEPT('FACT.TJ_Aktiv','FACT.TJ_Aktiv'[FACT_PATHLEVEL_1],'FACT.TJ_Aktiv'[DIM_TJ_Company_Key]),'DIM.TJ_Aktiv_LC'[Name] IN {"A","B","C"})
    			,2,CALCULATE(NOT ISEMPTY('FACT.TJ_Aktiv'),ALLEXCEPT('FACT.TJ_Aktiv','FACT.TJ_Aktiv'[FACT_PATHLEVEL_1],'FACT.TJ_Aktiv'[FACT_PATHLEVEL_2],'FACT.TJ_Aktiv'[DIM_TJ_Company_Key]),'DIM.TJ_Aktiv_LC'[Name] IN {"A","B","C"})
    			,3,CALCULATE(NOT ISEMPTY('FACT.TJ_Aktiv'),ALLEXCEPT('FACT.TJ_Aktiv','FACT.TJ_Aktiv'[FACT_PATHLEVEL_1],'FACT.TJ_Aktiv'[FACT_PATHLEVEL_2],'FACT.TJ_Aktiv'[FACT_PATHLEVEL_3],'FACT.TJ_Aktiv'[DIM_TJ_Company_Key]),'DIM.TJ_Aktiv_LC'[Name] IN {"A","B","C"})
    			,BLANK()
    		)
		)
		,[CC VM] = TRUE()
	)
)

[Anonymous]

If you have to write such monster formulas to determine a simple thing... it means the model is not right. But since you've not shown anything, no advice can be given.