Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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] ) )
)
)
)
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
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:
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.
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.
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
)
I created a new table in DAX and put the above code, however I receive:
Can you post the exact code you are using? I think you may be missing a close bracket or something
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
)
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]?
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]
)
)
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:
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] )
)
)
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
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 ?
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] ) )
)
)
)
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.
I can't replicate the problem.
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
Are you adding the code as a new column or a new measure? It is supposed to be a measure.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |