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...
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] ) )
        )
    )
)
johnt75's avatar
johnt75
Super User
3 years ago

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's avatar
Asina
Helper III
2 years ago

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