Calculating number of nodes in relationship data

[MBEL]

Hi all,

I have relationship data to plot in a network map. An example of this is:

Origin	Target
1	2
1	3
1	4
1	5
2	1

To use the network graph, I need to create a copy of the table, make the targets origins, and the origins target, like this:

Origin	Target
1	2
1	3
1	4
1	5
2	1
2	1
3	1
4	1
5	1
1	2

Now, I want to calculate the occurrences of each node, that is:

However this table is static and when I filter the origin node, I cannot make the count target update:

The right quantities should be:

Origin	Target	Count_Origin	Count_target
2	1	2	2
2	3	2	1
3	1	2	2
3	2	2	1

I have tried many things (countx / countrows and earlier functions, created separate tables and tried to join them, etc.

The formula I am using for the Count_Origin is :

Count_origin = COUNTX(filter(Countpairs, EARLIER(Countpairs[Origin]) = Countpairs[Origin]),Countpairs[Origin])

Any ideas, Please!!!

[v-jianboli-msft]

Hi @MBEL ,

Please try:

Count_origin = 
var _a = ALLSELECTED('Table'[Origin])
var _b = ALLSELECTED('Table'[Target])
return COUNTX(FILTER(ALL('Table'),[Origin] in _a&&[Target] in _b&&[Origin]=SELECTEDVALUE('Table'[Origin])),[Origin])

Count_target = 
var _a = ALLSELECTED('Table'[Origin])
var _b = ALLSELECTED('Table'[Target])
return COUNTX(FILTER(ALL('Table'),[Origin] in _a&&[Target] in _b&&[Origin]=SELECTEDVALUE('Table'[Target])),[Target])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MBEL]

Thanks for your message, but hat I am trying to calculate is the ocurrances of target nodes. In your filtered table what I am after is the following:

Origin	Target	Count_origin	Count_target
2	1	2	2	number of 1's in taget column
2	3	2	1	number of 3's in target column
3	1	3	2	number of 1's in taget column
3	2	3	1	nuber of 2's in target column

[v-jianboli-msft · Accepted solution]

Hi @MBEL ,

Sorry, due to my negligence there was an error in the fields in the previous measure.

Now it has been corrected, please check if the result is what you want now:

Count_origin = 
var _a = ALLSELECTED('Table'[Origin])
var _b = ALLSELECTED('Table'[Target])
return COUNTX(FILTER(ALL('Table'),[Origin] in _a&&[Target] in _b&&[Origin]=SELECTEDVALUE('Table'[Origin])),[Origin])

Count_target = 
var _a = ALLSELECTED('Table'[Origin])
var _b = ALLSELECTED('Table'[Target])
return COUNTX(FILTER(ALL('Table'),[Origin] in _a&&[Target] in _b&&[Target]=SELECTEDVALUE('Table'[Target])),[Target])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.