Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Return columns from multiple tables

Hi,   I have the following scenario -   1 master summary table with multiple rows, each row includes a type.   Multiple detail tables (1 per type), some containing a sub type, and some not.  1 ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Thanks for the suggestions - looking at them lead me down another path and I found the best solution (for me at least) is to create a series of measures to extract the sub-type for each row -

    Sub Type MST1 =
    CALCULATE (
        VALUES ('REC_TYPE1'[ST1] ) ,
        FILTER (
            'MASTER TABLE' ,
            'MASTER TABLE'[Rec ID] = SELECTEDVALUE ( 'MASTER TABLE'[Rec ID] )
        )
    )
     then MST2, MST3, MST4.
     
    And then use another measure to concatenate the individual type measures -
    Sub-Type =
    CONCATENATE (
        'MASTER TABLE'[MST1] ,
        CONCATENATE (
            'MASTER TABLE'[MST2] ,
            CONCATENATE (
                'MASTER TABLE'[MST3] ,
               'MASTER TABLE'[MST4]
            )
        )
    )
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous 

You may use below formula to create the table:

Table = UNION(TableA,TableB,TableD)

Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    unfortunately the detail tables are not the same layout - the different types have different numbers of columns, and not all of them have a sub-type.  Also I have no access to the query editor.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks for the suggestions - looking at them lead me down another path and I found the best solution (for me at least) is to create a series of measures to extract the sub-type for each row -

        Sub Type MST1 =
        CALCULATE (
            VALUES ('REC_TYPE1'[ST1] ) ,
            FILTER (
                'MASTER TABLE' ,
                'MASTER TABLE'[Rec ID] = SELECTEDVALUE ( 'MASTER TABLE'[Rec ID] )
            )
        )
         then MST2, MST3, MST4.
         
        And then use another measure to concatenate the individual type measures -
        Sub-Type =
        CONCATENATE (
            'MASTER TABLE'[MST1] ,
            CONCATENATE (
                'MASTER TABLE'[MST2] ,
                CONCATENATE (
                    'MASTER TABLE'[MST3] ,
                   'MASTER TABLE'[MST4]
                )
            )
        )