# Countif using DAX in Power Query



## jansi79 (Jul 19, 2016)

Hello,  does anyone know how I can write the formula:
COUNTIF([Child];[@Parent]) using DAX?
I have a list of 1.000.000 records that I need to identify row by row if the Parent is part of the Child

Regards

Jan Sigve


----------



## Comfy (Jul 19, 2016)

You could create two Queries:

Query 1 on the child Table (cTbl for the example)


Excel 2010A1Child2AAA3BBB4BBB5BBB6CCC7CCC8CCC9DDD10EEE11EEE12FFF13FFF14GGG15Sheet3

And group by Child and add a Count Column:


```
let
    Source = Excel.CurrentWorkbook(){[Name="cTbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Child"}, {{"cCount", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"
```

Query 2 on the Parent Table or field... (pTbl for this example)


Excel 2010A1Parent2AAA3BBB4CCC5EFR6TGY7GGG8Sheet4

The Join the Count column From Query 1:


```
let
    Source = Excel.CurrentWorkbook(){[Name="pTbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Parent"},cTbl,{"Child"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"cCount"}, {"NewColumn.cCount"})
in
    #"Expanded NewColumn"
```

The resulting Table:


Excel 2010EF1ParentNewColumn.cCount2AAA13BBB34CCC35GGG16EFR7TGY8Sheet4


----------



## jansi79 (Jul 20, 2016)

Hello, what I want to achieve is to identify the values in Parent, Grandparent and Great grandparent that is not in the Child column.
Does anyone know how I can do this using Power Query?

Regards
Jan Sigve 


ChildParentGrandparentGreat grandparentCount ParentCount GrandparentCount Great Grandparent131--261--3101--481--541--61318-117181--8101--9---10181--11---1213-1-13181--1425--01518--1162222-001723-0-182123-0019---20240--

<tbody>

</tbody>


----------

