Filter 2 related tables

[Anonymous]

Hello all,

I have the follow table:

ID	Invoice	Center A	Center B	Value A	Value B	Weight A	Weight B	Date A	Date B
1	50	83	38	40000,00	1500,00	4500,00	570,00	21/01/2020	29/01/2020
1	50	83	80	40000,00	1500,00	4500,00	570,00	21/01/2020	29/01/2020
1	50	83	83	40000,00	40000,00	4500,00	4500,00	21/01/2020	21/01/2020
1	50	83	35	40000,00	700,00	4500,00	20,00	21/01/2020	08/05/2020

I just need to see the line 3 of the table.

I wanna filter only the cases where Center A = Center B AND Date A = Date B (line 3 in the table).

It seems to be easy to resolve but i'm having this poblem because i have 2 differents tables:

Table A: Center A, Value A, Weight A and Date A

Table B: Center B, Value B, Weight B and Date B

So i can't create a measure with a siple Filter.

I don't know if i was clear enought but anyone can help me?

I took a look on the community but i didn't find anything that could help me 😞

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the merge function to meet your requirement.

Merge Table A and Table B in Power Query Editor based on Right outer.

Or if you want to show the output in a table visual like this,

You can create this measure and put it in Filter on this visual, configure the value is 1.

Measure = 
IF(
    MAX('Table A'[Invoice])=MAX('Table B'[Invoice]) &&
    MAX('Table A'[Date A])=MAX('Table B'[Date B]) &&
    MAX('Table A'[Center A])=MAX('Table B'[Center B]) &&
    MAX('Table A'[Value A])=MAX('Table B'[Value B]) && 
    MAX('Table A'[Weight A])=MAX('Table B'[Weight B]),1,0)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@Anonymous , can you share both tables (sample data) and expected output with sample filter

[Anonymous]

@amitchandak 

I have 2 tables:

Table A:

ID	Invoice	Center A	Value A	Weight A	Date A
1	50	83	40000,00	4500,00	21/01/2020

Table B:

Invoice	Center B	Value B	Weight B	Date B
50	38	1500,00	570,00	29/01/2020
50	80	1500,00	570,00	29/01/2020
50	83	40000,00	4500,00	21/01/2020
50	35	700,00	20,00	08/05/2020

So I joined the two tables and got the table below:

ID	Invoice	Center A	Center B	Value A	Value B	Weight A	Weight B	Date A	Date B
1	50	83	38	40000,00	1500,00	4500,00	570,00	21/01/2020	29/01/2020
1	50	83	80	40000,00	1500,00	4500,00	570,00	21/01/2020	29/01/2020
1	50	83	83	40000,00	40000,00	4500,00	4500,00	21/01/2020	21/01/2020
1	50	83	35	40000,00	700,00	4500,00	20,00	21/01/2020	08/05/2020

Now i need the follow output, where Center A = Center B and Date A = Date B:

ID	Invoice	Center A	Center B	Value A	Value B	Weight A	Weight B	Date A	Date B
1	50	83	83	40000,00	40000,00	4500,00	4500,00	21/01/2020	21/01/2020

Any question, please, let me know