Forum Discussion

jdormer's avatar
jdormer
Helper I
10 years ago

Engagement Role Report - Calculated Column question

I am having some difficulty creating a measure to display the individuals working on an engagement and their roles. I have the following tables:       The managers table lists Managers for ea...
Need Help
jdormer's avatar
jdormer
Helper I
10 years ago

v-sihou-msft and Vvelarde, 

 

Thank you both for taking the time to respond. This was a "want" of one of my managers, not a critical need. If I revisit this and come up with a solution I will post back. 

 

Thanks,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

jdormer

 

I’ve worked out this problem now. We can use CONCATENATEX function to concatenates multiple names in different rows. Please refer to following steps.

The relationship between three tables is like below.

 

  1. Create a calculated column in Managers Table which stores the user names.
     
    NameColumn =
RELATED ( Users[Name] )
  2. Create a measure which count rows for each engagement.
     
    EngagementRows = 
CALCULATE (
    COUNTROWS ( 'Managers Table' ),
    ALLEXCEPT ( 'Managers Table', 'Managers Table'[EngagementID] )
)
  3. Create a measure for sales names in each Engagement.
     
    Sales = 
CALCULATE (
    CONCATENATEX ( 'Managers Table', 'Managers Table'[NameColumn], ", " ),
    FILTER (
        'Managers Table',
        'Managers Table'[Allocation ID] = 1
            && 'Managers Table'[ID] <= MAX ( 'Managers Table'[ID] )
            && 'Managers Table'[ID]
                > MAX ( 'Managers Table'[ID] ) - [EngagementRows]
    )
)
  4. Create a measure for managers names in each Engagement.
     
    Manager = 
CALCULATE (
    CONCATENATEX ( 'Managers Table', 'Managers Table'[NameColumn], ", " ),
    FILTER (
        'Managers Table',
        'Managers Table'[Allocation ID] = 2
            && 'Managers Table'[ID] <= MAX ( 'Managers Table'[ID] )
            && 'Managers Table'[ID]
                > MAX ( 'Managers Table'[ID] ) - [EngagementRows]
    )
)
  5. Create a measure for architects names in each Engagement.
     
    Architect = 
CALCULATE (
        CONCATENATEX ( 'Managers Table', 'Managers Table'[NameColumn], ", " ),
        FILTER (
            'Managers Table',
            'Managers Table'[Allocation ID] = 3
                && 'Managers Table'[ID] <= MAX ( 'Managers Table'[ID] )
                && 'Managers Table'[ID]
                    > MAX ( 'Managers Table'[ID] ) - [EngagementRows]
        )
)
  6. Drag Table chart into your canvas and select values as below. Set Totals to “Off” in Table Format –> General.