Forum Discussion
Filter tables without relationships
Impactful IndividualIf I understadn what you need then you can use the following:
Let's say you have 3 tables:
Data: Source Name (text), Destinaton Name (text), Value (number) columns
Source: Name column (ditinct list of source names)
Destination: Name column (distinct list of destination names)
No relationships between tables.
Create 2 slicers - one based on Source[Name] table, second based on Destination[Name].
Put Data[Source Name] and Data[Destination] into corresponding fields of the visual.
And use this measure for Weight field of the slicer:
Sankey Chart Value = IF (
AND (
SELECTEDVALUE( Data[Destination Name] ) IN VALUES ( Source[Name] ),
SELECTEDVALUE( Data[Source Name] ) IN VALUES (Destination[Name] )
),
SELECTEDVALUE( Data[Value] ),
BLANK()
)
The chart will show only connections between sources/destinations selected in the slicers, otherwise the measure returns BLANK() and the pair dissapears from the chart.
This is just a base. You can modify the measure as you wnat (e.g. look up a value in 4th table) or check other slicers.
Let me know if it works for you,
Hi avatorl thanks for your answer, I was able to replicate your suggestion. Nevertheless I still can't filter the Source and Destination tables. I'll try to explain the issue more in detail.
I'm replicating a production process made of 4 steps by means of tables connected the one to the others to simulate how production lots produced in step "n" flow to lot "n+1". Simplifying, I have tables with this kind of relationships (actually intermediate tables are needed as well):
ProdStep1 --> ProdStep2 --> ProdStep3 --> ProdStep4
When I click on a visual with lot names from ProdStep1, I'll see the filter propagating to the other tables to show which are the downstream lots that made use of the selected lot.
The "Sankey" Source and Destination tables contain all the (distinct) lots produced in the 4 production steps. Unfortunately I can't simply create relationships between the lots in the ProdStep tables and the Source and Destination tables, or I'll have an issue due to relationships conflict.
So I need to manually select, as you wrote, the lots from Source and Destination tables, but this is not helping.
Is there a way to propagate the filter from ProdStep1, 2, 3 & 4 to Source and Destination without making use of relationships?
Thanks again!
- avatorl
I'm not sure I understand what you need well but It looks like you don't want to filter your source and destination tables manually. But you want to get them filtered based on a filter applied to ProdStep1 table.
Then you probably don't need Source and Destination tables at all. Use your ProdStep* tables.
Let's say you have a filter applied to ProdStep1 table. And it has relationships with ProdStep2 and ProdStep3 and ProdStep4. So these 3 tables are fltered by the filter applied to ProdStep1. I will use only 3 tables in my example:
My 'All Products' table has 2 columns and (distinct number of products) * (distinct number of products) rows (10K rows for 100 products). For each ID in Column 1 there is a list of all IDs in Column 2.
Now this measure:Measure := VAR _source = SELECTEDVALUE ( 'All Products'[Column1] ) VAR _destination = SELECTEDVALUE ( 'All Products'[Column2] ) RETURN IF ( AND ( _source IN VALUES ( Table01[Column1] ), _destination IN SELECTCOLUMNS ( FILTER ( Table02, Table02[Column2] = _source ), "id", Table02[Column1] ) ) || AND ( _source IN VALUES ( Table02[Column1] ), _destination IN SELECTCOLUMNS ( FILTER ( Table03, Table03[Column2] = _source ), "id", Table03[Column1] ) ), 1, BLANK () )(i'm using 1 as a value so all connections have the same width)
will show me my multiple steps process for the selected items in the first table:
just add one more OR ( || ) to add one more step- avatorl
I've upadted the above measure (and screenshot). Now it should work well .