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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Memorable Member
Memorable Member

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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