Get Multiple Value from Multiple Unique value

[Wicak]

how to get multiple values ​​from 3 tables based on unique values​?

   

  

Result =

VAR A =

Calculate(

    MAX('Table B'[Size]),

    Filter('Table B','Table B'[Unique Number] ='Table A'[Unique Number])

VAR B =

Calculate(

    MAX('Table C'[Size]),

    Filter('Table C','Table C'[Unique Number] ='Table A'[Unique Number])

VAR C =

Calculate(

    MAX('Table D'[Size]),

    Filter('Table D','Table D'[Unique Number] ='Table A'[Unique Number])

RETURN

A & ""& B &""& C

I tried DAX MAX with a filter with Var a , Var B and Var C for each table then returning the results to result column, but the value is only MAX. i want all values ​​to be displayed on a per unique value basis in the Result column of Table A.
I know, its can do just by connecting with relations and show with tabel visual, but I need this DAX method for show in tabel view.

can you give me some insight About this?
Thank you for your attention

[Wicak]

hi @Anonymous 

thanks for advice, at the end i do your methode combine with relationship 😁

[StrategicSavvy]

hi @Wicak , 

To get this you need to create new table and use UNION formula.

here is the code :

Table A = 
    UNION(
    'Table B',
    'Table C',
    'Table D'
)

This code will return multiple values from 3 tables

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: www.linkedin.com/in/lukasz-kozdron 

[Wicak]

hi @StrategicSavvy 

thank you for your reply. so sory table B, C and D is dumy, actually their table have multiple column. i try in DAX PBI and DAX has a get error pop up

is there any way other to solve this problem?

[Anonymous · Accepted solution]

Hi @Wicak ,

You can make a little adjustment on the formula which provided by @StrategicSavvy , please note that what he is creating is a calculated table.

Table A =
UNION (
    SUMMARIZE ( 'Table B', 'Table B'[Unique Number], 'Table B'[Size] ),
    SUMMARIZE ( 'Table C', 'Table C'[Unique Number], 'Table C'[Size] ),
    SUMMARIZE ( 'Table D', 'Table D'[Unique Number], 'Table D'[Size] )
)

Best Regards