Countif using DAX in Power Query

[jansi79]

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]

You could create two Queries:

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

Excel 2010
	A
1	Child
2	AAA
3	BBB
4	BBB
5	BBB
6	CCC
7	CCC
8	CCC
9	DDD
10	EEE
11	EEE
12	FFF
13	FFF
14	GGG
15
Sheet3

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 2010
	A
1	Parent
2	AAA
3	BBB
4	CCC
5	EFR
6	TGY
7	GGG
8
Sheet4

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 2010
	E	F
1	Parent	NewColumn.cCount
2	AAA	1
3	BBB	3
4	CCC	3
5	GGG	1
6	EFR
7	TGY
8
Sheet4

 

[jansi79]

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

Child	Parent	Grandparent	Great grandparent	Count Parent	Count Grandparent	Count Great Grandparent
1	3			1	-	-
2	6			1	-	-
3	10			1	-	-
4	8			1	-	-
5	4			1	-	-
6		13	18	-	1	1
7	18			1	-	-
8	10			1	-	-
9				-	-	-
10	18			1	-	-
11				-	-	-
12		13		-	1	-
13	18			1	-	-
14			25	-	-	0
15			18	-	-	1
16		22	22	-	0	0
17		23		-	0	-
18		21	23	-	0	0
19				-	-	-
20	24			0	-	-

<tbody>
</tbody>