Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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.
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
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |