Forum Discussion

Wicak's avatar
Wicak
Helper I
2 years ago
Solved

Get Multiple Value from Multiple Unique value

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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • StrategicSavvy's avatar
    StrategicSavvy
    Resolver II
    2 years ago

    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's avatar
      Wicak
      Helper I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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

  • Wicak's avatar
    Wicak
    Helper I
    2 years ago

    hi Anonymous 

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