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

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

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

The end of the input was reached

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

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

    • Asina's avatar
      Asina
      Helper III
      3 years ago
      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's avatar
        johnt75
        Super User
        3 years ago

        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
)