Finding paths connected through a node. Data filtering

[Asina]

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.

Main	Str1	Str2
Dan5	352	564
Dan5	564	585
Ras10	5265	5263
Ras10	5263	585
Ras15	745	743
Ras15	743	742
Ras15	742	585

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

[johnt75 · Accepted solution]

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 · Accepted solution]

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]

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]

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.

[Asina]

Thanks for answering, however I am not sure I fully get what you mean.. Do you have an example?

Also is there a way to fix the following where the Mains Neighbor shows a comma between the Main, since it is not filterable. I know some nodes might have many connections to other Mains, but is there a way to show only 1 Neighbor per row, but make the row duplicate itself if more than 1 connection is spotted as to avoid the comma issue? See picture below. 

[johnt75]

There's no way to solve the issue of multiple entries using a measure. You could instead create a calculated table containing all the mains and all the connected entities, I think the below should work

Main Neighbours =
GENERATE (
    ALLNOBLANKROW ( 'Table'[Main] ),
    VAR InitialNodes =
        UNION (
            CALCULATETABLE ( VALUES ( 'Table'[Str1] ) ),
            CALCULATETABLE ( VALUES ( 'Table'[Str2] ) )
        )
    VAR ConnectedMains =
        CALCULATETABLE (
            VALUES ( 'Table'[Main] ),
            'Table'[Str1]
                IN InitialNodes
                || 'Table'[Str2] IN InitialNodes,
            REMOVEFILTERS ( 'Table' )
        )
    VAR ConnectedMainsExlcudingCurrent =
        EXCEPT ( ConnectedMains, { 'Table'[Main] } )
    RETURN
        ConnectedMainsExlcudingCurrent
)

[Asina]

I created a new table in DAX and put the above code, however I receive: 

The end of the input was reached

[johnt75]

Can you post the exact code you are using? I think you may be missing a close bracket or something

[Asina]

Main Neighbours =

GENERATE (

    ALLNOBLANKROW ( dbo_Cables_10kV[Radialnr-rettet] ),

    VAR InitialNodes =

        UNION (

            CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.1] ) ),

            CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.2] ) )

        )

    VAR ConnectedMains =

        CALCULATETABLE (

            VALUES ( dbo_Cables_10kV[Radialnr-rettet] ),

            dbo_Cables_10kV[Straekning.1]

                IN InitialNodes

                || dbo_Cables_10kV[Straekning.2] IN InitialNodes,

            REMOVEFILTERS ( dbo_Cables_10kV )

        )

    VAR ConnectedMainsExlcudingCurrent =

        EXCEPT ( ConnectedMains, { dbo_Cables_10kV[Radialnr-rettet]] } )

    RETURN

        ConnectedMainsExlcudingCurrent

)

[johnt75]

You have an extra ]

Main Neighbours =
GENERATE (
    ALLNOBLANKROW ( dbo_Cables_10kV[Radialnr-rettet] ),
    VAR InitialNodes =
        UNION (
            CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.1] ) ),
            CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.2] ) )
        )
    VAR ConnectedMains =
        CALCULATETABLE (
            VALUES ( dbo_Cables_10kV[Radialnr-rettet] ),
            dbo_Cables_10kV[Straekning.1]
                IN InitialNodes
                || dbo_Cables_10kV[Straekning.2] IN InitialNodes,
            REMOVEFILTERS ( dbo_Cables_10kV )
        )
    VAR ConnectedMainsExlcudingCurrent =
        EXCEPT ( ConnectedMains, { dbo_Cables_10kV[Radialnr-rettet] } )
    RETURN
        ConnectedMainsExlcudingCurrent
)

[Asina]

Ah I see thank you. But you dont get the error: Funktionen GENERATE does not allow two columns with the same name 'dbo_Cables_10kV'[Radialnr-rettet]?

[johnt75]

Ah, OK.

Main Neighbours =
GENERATE (
    ALLNOBLANKROW ( dbo_Cables_10kV[Radialnr-rettet] ),
    VAR InitialNodes =
        UNION (
            CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.1] ) ),
            CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.2] ) )
        )
    VAR ConnectedMains =
        CALCULATETABLE (
            VALUES ( dbo_Cables_10kV[Radialnr-rettet] ),
            dbo_Cables_10kV[Straekning.1]
                IN InitialNodes
                || dbo_Cables_10kV[Straekning.2] IN InitialNodes,
            REMOVEFILTERS ( dbo_Cables_10kV )
        )
    VAR ConnectedMainsExlcudingCurrent =
        EXCEPT ( ConnectedMains, { dbo_Cables_10kV[Radialnr-rettet] } )
    RETURN
        SELECTCOLUMNS (
            ConnectedMainsExlcudingCurrent,
            "Connected to", dbo_Cables_10kV[Radialnr-rettet]
        )
)

[Asina]

Hi @johnt75 

a quick question. 

If I wanted to see the Neighbors Neighbor in a new column, in the new table we created. How do I add it in the base code of the new table? Since as far as I know, the size of the table is tangible when its in forming process i.e. creation of a new table.

Also If I want another column in the same table showing all the filtered values:  ( Main -> Neighbor of Main -> Neighbors Neighbor ) that would be perfect...

This way I can just connect this column to the picture table and have it all solved in 1 go. 

is it possible?

I hope I am not bothering you too much..But you have been through this with me, and know how it works 🙂 thanks

example:

[johnt75]

I'm not sure what you mean by the second column, but in principle you could get the neighbours neighbour column by nesting GENERATE statements.  Not sure if the below will work but you can try

Main Neighbours =
GENERATE (
    GENERATE (
        ALLNOBLANKROW ( dbo_Cables_10kV[Radialnr-rettet] ),
        VAR InitialNodes =
            UNION (
                CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.1] ) ),
                CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.2] ) )
            )
        VAR ConnectedMains =
            CALCULATETABLE (
                VALUES ( dbo_Cables_10kV[Radialnr-rettet] ),
                dbo_Cables_10kV[Straekning.1]
                    IN InitialNodes
                    || dbo_Cables_10kV[Straekning.2] IN InitialNodes,
                REMOVEFILTERS ( dbo_Cables_10kV )
            )
        VAR ConnectedMainsExlcudingCurrent =
            EXCEPT ( ConnectedMains, { dbo_Cables_10kV[Radialnr-rettet] } )
        RETURN
            SELECTCOLUMNS (
                ConnectedMainsExlcudingCurrent,
                "Connected to", dbo_Cables_10kV[Radialnr-rettet]
            )
    ),
    CALCULATETABLE (
        VAR InitialNodes =
            UNION (
                CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.1] ) ),
                CALCULATETABLE ( VALUES ( dbo_Cables_10kV[Straekning.2] ) )
            )
        VAR ConnectedMains =
            CALCULATETABLE (
                VALUES ( dbo_Cables_10kV[Radialnr-rettet] ),
                dbo_Cables_10kV[Straekning.1]
                    IN InitialNodes
                    || dbo_Cables_10kV[Straekning.2] IN InitialNodes,
                REMOVEFILTERS ( dbo_Cables_10kV )
            )
        VAR ConnectedMainsExlcudingCurrent =
            EXCEPT (
                ConnectedMains,
                { dbo_Cables_10kV[Radialnr-rettet], SELECTEDVALUE ( [Radialnr-rettet] ) }
            )
        RETURN
            SELECTCOLUMNS (
                ConnectedMainsExlcudingCurrent,
                "Neighbours neighbour", dbo_Cables_10kV[Radialnr-rettet]
            ),
        TREATAS ( { [Connected to] }, dbo_Cables_10kV[Radialnr-rettet] )
    )
)

[Asina]

Hi @johnt75 

I wonder if you remember the above solution you provided. 

I have an issue where Connected To column produces a blank which causes it to make the Neighbours neighbour to be wrong, as it uses the blank in connected to in Treatas(), which ends up giving wrong neighbours which is additional to the ones that are right. 

And i can only filter with All to All in the Model. Any solution to the code which says ignore blanks in connected to or remove it all together?
Thanks again

[Asina]

@johnt75 

Thank you, it does work for the Neighbors Neighbor column  👍. 

What I meant with a second column, was that a column that sums the total of filtered.

In this: Main + Neighbor of Main + Neighbors Neighbor. This makes the table large. But, I can use this new column to connect to the picture table and then have the problem solved. is it possible ?

[johnt75 · Accepted solution]

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] ) )
        )
    )
)

[Asina]

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. 

[johnt75]

I can't replicate the problem.

[Asina]

I am getting 1 line where all the Main values are written, instead of only the ones connected to the current Main. 
see the picture

[johnt75]

Are you adding the code as a new column or a new measure? It is supposed to be a measure.