topic Re: Filter tables without relationships in DAX Commands and Tips

Filter tables without relationships

OnlyPhilip — Sat, 13 Jun 2020 14:08:05 GMT

Hi, I'd like to create a "dynamic" table which should be filtered by other tables, but without using relationships (I have too many of them in place already).

The reason for this is that I need this "dynamic table" as input for a Sankey Chart visual.

I've tried with DAX and TREATAS but I realized that the table created is then static, so not reacting to filters applied to the Source table in the report:

Dynamic table = CALCULATETABLE( Destination, TREATAS(VALUES(Source[ID], Destination[ID]) )

Thanks!

Re: Filter tables without relationships

lbendlin — Sat, 13 Jun 2020 16:27:34 GMT

Your TREATAS() implementation doesn't look right. Please check the documentation again. As always you can make your source table dynamic, based on current filter context or other criteria.

Re: Filter tables without relationships

mahoneypat — Sun, 14 Jun 2020 01:22:45 GMT

Calculated tables like that one are static, but you likely want to use TREATAS in a measure instead, as follows:

New Measure = CALCULATE([YourMeasure], TREATAS(VALUES(Source[ID]), Destination[ID]))

Use that pattern for each of the measures that work on the Destination table that you need. You would need to use slicers, etc. from your Source table if you need to select certain values.

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Filter tables without relationships

stevedep — Sun, 14 Jun 2020 09:09:46 GMT

Can you explain why you need a dynamic table for your sankey to work? A sankey requires a source and destination which should be in your data, this dataset can be filtered using your slicers. Your question implies you need to dynamically define the source and destination?

Re: Filter tables without relationships

avatorl — Sun, 14 Jun 2020 11:13:07 GMT

If 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,

Re: Filter tables without relationships

OnlyPhilip — Mon, 15 Jun 2020 14:53:38 GMT

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!

Re: Filter tables without relationships

avatorl — Sun, 21 Jun 2020 16:35:47 GMT

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

Re: Filter tables without relationships

avatorl — Sun, 21 Jun 2020 16:17:22 GMT

I've upadted the above measure (and screenshot). Now it should work well .