Created a filtered table based on the presence of correlation elements with another table

[IlMoro]

Hi,

I have two tables, connected through a bi-directional cross filter many-to-one relationship on one column (Table P, column PID (one) - Table IN, column PID (many)) 

The table P contains too many items, even those that I am not interested in.

I am insterested in creating a filtered table ("P-filtered") that contains just those records that has a corresponding record in the table IN.

Table IN

INID	PID	OpenedDate
IN01	PID02	2022/01/03
IN02		2022/01/15
IN03	PID03	2022/01/22
IN04	PID03	2022/02/19

Table P

PID	CreatedDate	Hours
PID01	2022/01/30	5
PID02	2022/02/30	3

Using the example below I need to create a filtered P table that contains just the record PID02 (keeping all columns from table P)

Thanks for any support.

[danielwelch · Accepted solution]

Hi @IlMoro,

Try chaging the operator from >1 to >0.  Either that or make sure the format of the two columns is the same.

[danielwelch]

Hi,

Can you not add a column to your P table which evaluates whether the record exisits on the IN table e.g
IF(
CALCULATE(Max(Table IN Opened Date),filter(all('Table IN'),
P[PID]='IN'[PID]))>1,"Yes","No")

then you can create a new table with a filter on this new column ="Yes" .

[IlMoro]

@danielwelch the idea is preatty good and could solve my issue however "Opened Date" in the formula does not fit with the request.
I need to take into consideration just PID & INID on both tables.

Opened date & createed dates are just examples and could be empty in reality.

[danielwelch]

@IlMoro 

I only used the date as it was easy to aggregate.  It could be distinct count and based on the PID e.g

IF(
CALCULATE(Distinctcount(PID),filter(all('Table IN'),
P[PID]='IN'[PID]))>1,"Yes","No").  

Hope this helps.

[IlMoro]

Hi @danielwelch  and thanks for the info. This is the expression I managed to modify without  having errors however it displays just "NO", even in rows with PID02 that exists in the table "IN".

= IF(

CALCULATE(Distinctcount('Table IN'[PID]),filter(all('Table IN'),

'Table P'[PID]='Table IN'[PID]))>1,"Yes","No")

[danielwelch · Accepted solution]

Hi @IlMoro,

Try chaging the operator from >1 to >0.  Either that or make sure the format of the two columns is the same.

[IlMoro]

Hi @danielwelch , thanks for the patience. This worked and I can now filter thr table, thanks a lot.

I gave you points however I'm still searching for the possibility to create a filtered table since every time I have to remeber to filter out "No"-columns from the visualization...

[danielwelch · Accepted solution]

HI @IlMoro ,

Im not sure I fully understand the issue still but now that you have the calculated column it can be refrenced in a measure or in a table calculation or as a visual filter from the filter pane.  Once you have set up one of these you should not have to do it again each time you refresh.

I have attached examples.

hope this helps.

[IlMoro]

It helped a lot, thanks 😉

[IlMoro]

Created a custom column in your file, copied text above however this is the result:

[danielwelch]

@IlMoro 

The dax is not for Power Query editor.  If you do this wihthin Data view within BI Desktop.

[vojtechsima]

Hi, @IlMoro 
you can merge two tables as new and unfilter empty records:

Check attached file.

[IlMoro]

Hi @vojtechsima,

the problem is there can be more records in the table P for the same record in table IN so I cannot merge them, I have to manage these situations.
What I need, even for other purposes, is a filtered table that reflects the table P but removes all the exceeded records that have no a related record in IN.

[vojtechsima]

Hi, @IlMoro 
Did you check my file? I think it does exactly that, since P should be unique right?

[IlMoro]

@vojtechsima we have to manage the possibility that there will be multiple PID related to multiple INID so P is not unique.
Moreover, for different reasons, I have to keep the two tables disjointed that's why I need a filtered table.