Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
IlMoro
Helper II
Helper II

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

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

INIDPIDOpenedDate
IN01PID022022/01/03
IN02 2022/01/15
IN03PID032022/01/22
IN04PID032022/02/19

 

Table P

PIDCreatedDateHours
PID012022/01/305
PID022022/02/303

 

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.

2 ACCEPTED SOLUTIONS

Hi @IlMoro,

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





View solution in original post

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.Screenshot 2022-08-15 145306.pngScreenshot 2022-08-15 145222.png

hope this helps.

View solution in original post

14 REPLIES 14
danielwelch
Resolver II
Resolver II

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" .

@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.

@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.

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")
 
Capture.JPG

Hi @IlMoro,

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





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...

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.Screenshot 2022-08-15 145306.pngScreenshot 2022-08-15 145222.png

hope this helps.

It helped a lot, thanks 😉

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

Capture.JPG

@IlMoro 

The dax is not for Power Query editor.  If you do this wihthin Data view within BI Desktop.Screenshot 2022-08-12 141341.png

vojtechsima
Super User
Super User

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

vojtechsima_0-1660228835603.png

Check attached file.

 

 

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.

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

 

@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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.