Forum Discussion

Asina's avatar
Asina
Helper III
3 years ago
Solved

Finding paths connected through a node. Data filtering

 

I have a table which resembles the below.

The columns explained:

Main contains the name and the pathway number. The Main can therefore contain nodes called Str1 or Str2, which indicates where it is connected on the node. See the picture below the tables.

 

MainStr1Str2
Dan5352564
Dan5564585
Ras1052655263
Ras105263585
Ras15745743
Ras15743742
Ras15742585

 

I am interested to know when i choose Dan5, the results show me a list of connected Mains called Main2.

 

example: 

If I press on Dan5, I will get Ras15 and Ras10 as result, since they are mutually connected through node 585. 

If for example Ras10 was not connected through node 585, Then naturally the result would be Ras15.

Now, the above mentioned is just a fraction of the data, I have a data that contains atleast 300+ main names.

So a simple if() statement for the above mentioned isnt what I am looking for. 

Any other methods that can apply for XXX amount Main names?

 

Thanks

General Comment
Need Help
Show and Tell
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Connected Mains = 
VAR InitialNodes =
	UNION( VALUES( 'Table'[Str1] ), VALUES( 'Table'[Str2] ) )
VAR ConnectedMains =
	CALCULATETABLE(
		VALUES( 'Table'[Main] ),
		'Table'[Str1] IN InitialNodes
			|| 'Table'[Str2] IN InitialNodes,
		REMOVEFILTERS( 'Table' )
	)
VAR ConnectedMainsExlcudingCurrent =
	EXCEPT( ConnectedMains, { SELECTEDVALUE( 'Table'[Main] ) } )
VAR Result =
	CONCATENATEX(
		ConnectedMainsExlcudingCurrent,
		'Table'[Main],
		", "
	)
RETURN
	Result
  • johnt75's avatar
    johnt75
    3 years ago

    If I understand correctly you want a list of mains and all the nodes they are connected to. You could do another table like

    Main All Connected =
GENERATE (
    ALLNOBLANKROW ( 'Main Neighbours'[Radialnr-rettet] ),
    DISTINCT (
        UNION (
            CALCULATETABLE ( VALUES ( 'Main Neighbours'[Connected To] ) ),
            CALCULATETABLE ( VALUES ( 'Main Neighbours'[Neighbours Neighbour] ) )
        )
    )
)

21 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try

    Connected Mains = 
VAR InitialNodes =
	UNION( VALUES( 'Table'[Str1] ), VALUES( 'Table'[Str2] ) )
VAR ConnectedMains =
	CALCULATETABLE(
		VALUES( 'Table'[Main] ),
		'Table'[Str1] IN InitialNodes
			|| 'Table'[Str2] IN InitialNodes,
		REMOVEFILTERS( 'Table' )
	)
VAR ConnectedMainsExlcudingCurrent =
	EXCEPT( ConnectedMains, { SELECTEDVALUE( 'Table'[Main] ) } )
VAR Result =
	CONCATENATEX(
		ConnectedMainsExlcudingCurrent,
		'Table'[Main],
		", "
	)
RETURN
	Result
    • Asina's avatar
      Asina
      Helper III
      3 years ago

      Hi johnt75 

      Thank you for replying. 

      You are on the right track, however not quite there yet.

      Using the above mentioned code gives me the entire list of Main, not the ones that the selected Main is connected to. 

      So to check what I mean..you can increase the row of the table above by 3 and call the new Main Ras20 then make a new path that has no values in Str1 and Str2 that is the same as the rows above.

      You will see that the code gives you still the Main that is not even connected to it. 

       

    • Asina's avatar
      Asina
      Helper III
      3 years ago

      Hi johnt75 

      I have a followup issue, which I cannot seem to solve.

      The above solution you provided gives me the "Neighbors" of the Main, however when I want the Neighbors-Neighbor, I just put the Main in a new table or matrix and get the Neighbors-Neighbor. This is viable but not practical.
      However, in this method I will have to make 3 tables just to show these information, and I only have the choice to click on Neighbors-Neighbor which filters the picture, yet I have no choice in having Neighbor table to show me the picture.
      Is there a way to collate them all into 1 view or 1 table/matrix and each Main is separately clickable.
      I have a picture you can see what I mean.

       

      Is the filter collider a good idea to see the Neighbors-neighbor?

      however at the moment it shows the previous chosen Main:

       

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        No idea if this will work but these are my thoughts, for what they're worth.

        You could try creating a field parameter which includes columns from Main, Main's neighbour and Neighbour's neighbour. Put the field parameter on each of the slicers, and set a filter on the field parameter in each slicer, so each one shows a different column.

        On the chart, also use the field parameter as the value. I'm hoping that when you select one of the values from the slicer that selection, along with the field parameter filter, will get applied to the chart and show the correct info.