Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter 2 related tables

Hello all,

 

I have the follow table:

IDInvoiceCenter ACenter BValue AValue BWeight AWeight BDate ADate B

1

50833840000,001500,004500,00570,0021/01/202029/01/2020
150838040000,001500,004500,00570,0021/01/202029/01/2020
150838340000,0040000,004500,004500,0021/01/202021/01/2020
150833540000,00700,004500,0020,0021/01/202008/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 😞

 

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

       

      I have 2 tables:

      Table A:

      IDInvoiceCenter AValue AWeight ADate A
      1508340000,004500,0021/01/2020

       

      Table B:

      InvoiceCenter BValue BWeight BDate B
      50381500,00570,0029/01/2020
      50801500,00570,0029/01/2020
      508340000,004500,0021/01/2020
      5035700,0020,0008/05/2020

       

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

      IDInvoice Center ACenter BValue AValue BWeight AWeight BDate ADate B
      150833840000,001500,004500,00570,0021/01/202029/01/2020
      150838040000,001500,004500,00570,0021/01/202029/01/2020
      150838340000,0040000,004500,004500,0021/01/202021/01/2020
      150833540000,00700,004500,0020,0021/01/202008/05/2020

       

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

      IDInvoice Center ACenter BValue AValue BWeight AWeight BDate ADate B
      150838340000,0040000,004500,004500,0021/01/202021/01/2020

       

       

      Any question, please, let me know

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.