cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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.

 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

2 ACCEPTED SOLUTIONS
Super User

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``````
Super User

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 21
Super User

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``````
Helper III

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:

Super User

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.

Helper III

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.

Super User

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
)
``````
Helper III

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

# The end of the input was reached

Super User

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

Helper III
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
)
Super User

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
)
``````
Helper III

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

Super User

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,
)
)
``````
Helper III

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:

Super User

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,
)
),
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,
),
TREATAS ( { [Connected to] }, dbo_Cables_10kV[Radialnr-rettet] )
)
)
``````
Helper III

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

Helper III

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 ?

Super User

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] ) )
)
)
)
``````
Helper III

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.

Super User

I can't replicate the problem.

Helper III

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.