topic Re: How to get number of rows in a table based on a single column used in two different types? in DAX Commands and Tips

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

Aksh_1234 — Tue, 19 Mar 2024 13:42:44 GMT

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

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

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

Daoud_H — Tue, 19 Mar 2024 14:12:06 GMT

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.

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

Aksh_1234 — Tue, 19 Mar 2024 14:29:20 GMT

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

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

Aksh_12345 — Wed, 20 Mar 2024 06:54:15 GMT

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