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]
            )
        )
    )
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]
              )
          )
      )
      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous 

        Glad to hear the reply is helpful, please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

        Regards,