How to get number of rows in a table based on a single column used in two different types?

[Aksh_1234]

Hi all, 

Need one quick help here, In my data, I have kind of a Parent-Child like structure in my fact table in a single column, but I have created another table which defines parent child relationship as below:

Now, in my fact table, I have only index column and I have two text columns to compare the actual and expected values and define them as TRUE or FALSE.

So, fact table is like
Index --- Actual --- Expected

18

19  ---- Yes ----- Yes

20 ----- Yes ----- No

21 ----- No ----- No

So, for index 19,20,21, I can easily say that 19 and 21 is True and 20 is False. But, for 18 (which is parent for 19,20,21), I need to see if all the child indexes the true, then only I will be able to mark it as true. For this, I am trying an approach where I am getting the count of child indexes first (which I am able to do using DAX and parent child relation table) and then get the count of True rows for those child (this is where I need help), and if both these counts are equal then I am marking 18 as True else False. 

Can you please help me with DAX where I can get the count of Trues for all the child under any given parent?

Regards

[Aksh_12345]

Hey @Daoud_H you can find the PBIX file here -

https://drive.google.com/file/d/1D7-ibijR8lgHJ1NeB5u6_EcoLa3PVoYV/view?usp=drivesdk

Uploading from different id couldnt use the same id

[Daoud_H]

Hi @Aksh_1234, 

Assuming you have a parent-child relationship table named "ParentChildRelationship" with columns "ParentIndex" and "ChildIndex", and your fact table is named "FactTable" with columns "Index", "Actual", and "Expected", you can create a measure like this:

CountTrueForChildren = 
VAR ParentIndex = SELECTEDVALUE('FactTable'[Index])
VAR ChildRows = 
    CALCULATE(
        COUNTROWS('FactTable'),
        FILTER(
            'ParentChildRelationship',
            'ParentChildRelationship'[ParentIndex] = ParentIndex
        )
    )
VAR TrueChildRows = 
    CALCULATE(
        COUNTROWS('FactTable'),
        FILTER(
            'ParentChildRelationship',
            'ParentChildRelationship'[ParentIndex] = ParentIndex &&
            'FactTable'[Actual] = "Yes" &&
            'FactTable'[Expected] = "Yes"
        )
    )
RETURN
IF(ChildRows = TrueChildRows, TRUE(), FALSE())

Hope it helps.

[Aksh_1234]

Hey @Daoud_H ,

Thanks for the prompt response, but I have tried this already, but it is not working and giving some incorrect number in front of rows even where it is not a parent row. For example, I am getting some random number in front of 19 and 20 instead of 18.

I will try to upload the PBIX file with some sample data if possible, but I think since the fact table is having so many extra columns, maybe that is causing some extra filteration or something to happen