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 type doesn't have a matching detail table.  The rows in the detail tables use the same unique identifier as the corresponding row in the summary table.

 

Example

Summary table - 

Identifier     Desc                      Type 

1                 Row 1                    A

2                 Row 2                    A

3                 Row 3                    B

4                 Row 4                    C

5                 Row 5                    D

6                 Row 6                    D

7                 Row 7                    E

 

Detail table Type A -

Identifier    Sub Type

1                 A-1

2                 A-2

 

Detail table Type B - 

Identifier    Sub Type

3                 B-1

 

Detail table Type C -

Identifier

4                  No sub type

 

Detail table Type D -

Identifier    Sub Type

5                 D-1

6                 D-2

 

there is no detail table for type E.

 

What I am trying to do is build a table that can be filtered on the sub type (amongst other filters) where appropriate - so:

 

Slicer selects sub type A-1 - list displays row 1 only.

Identifier     Desc                      Type     Sub Type

1                 Row 1                    A           A-1

 

Slicer selects sub type D-1 - list displays rows 5 & 6 only.

Identifier     Desc                      Type     Sub Type

5                 Row 5                    D           D-1

6                 Row 6                    D           D-1

 

Slicer selects blank sub type - list displays rows 4 & 7 only

Identifier     Desc                      Type     Sub Type

4                 Row 4                    C

7                 Row 7                    E

 

Slicer has no select - list displays all rows. 

Identifier     Desc                      Type     Sub Type

1                 Row 1                    A           A-1

2                 Row 2                    A           A-2

3                 Row 3                    B           B-1

4                 Row 4                    C

5                 Row 5                    D           D-1

6                 Row 6                    D           D-1

7                 Row 7                    E

 

I'm new to Power BI, but wouldn't have any problem with SQL or SSRS.  I'm restricted to using a supplier developed model - I can create new measures, but not new columns.

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]
            )
        )
    )

10 Replies

  • 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

      Unfortunately I don't have access to the query editor for this application.  The supplier has provided a data model with no ability for me to change the underlying queries.