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]
            )
        )
    )
Washivale's avatar
Washivale
Resolver V
7 years ago

Hello Anonymous ,

 

one way to achieve this is consolidate tables A to D using Union, Then using Manage relationships define relationship between summary table and Consolidated table on Identifier.

 

then create visual with fields "Identifier , Desc, Type " from Summary table and "Sub Type" from Consolidated Table

 

Use Subtype from consolidated table as your table Slicer.

 

let me know if it helps

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,  thanks - but is this in he query editor?  I have no access to the query editor as the supplier has provided a data model that I have no access to change.  As previously mentioned, I can create measures but not calculated columns.

    • 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.