Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdormer
Helper I
Helper I

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:

 

 Engagement Table.PNG

 

The managers table lists Managers for each engagement. The AllocationID indicates the individuals role on the team: Salesperson (1), Project Manager (2), or Architect (3). 

 

 Managers Table.PNG

 

 

Users Table.PNG

 

I'd like to create a report that displays the Salesperson, Project Manager, and Architect for each engagement, as seen below. To do so, I'm trying to create a Salesperson column (or measure?), PM column, and architect column. I've looked at the functions CALCULATE, FILTER, RELATED, and others but I'm having difficulty determining the correct formula to populate these columns with the correct information. 

 

Output.PNG

 

Let me know if any additional information would be helpful. 

 

Thanks.

11 REPLIES 11
v-sihou-msft
Employee
Employee

@jdormer

 

In Power BI Desktop, we don’t have such a visual which can completely show result as you expect. If we use a Matrix, we can set “Engagement Name” as Rows and “Role” as Columns. But the Values always aggregates and only accepts numeric values, which means if we put “User Name” into Values, it will always be converted to “Count of User Name” instead of showing the actual User Name.

 

You may think that we can create a table report with a Salesperson measure, PM measure, and architect measure. But this is feasible only when there is no duplicate Allocation IDs in each Engagements. We cannot make the measure aggregate multiple Names when there’re duplicate Allocations.

 

100.jpg

 

We can use an alternate solution by creating a Matrix table as below, which marks the User Name with “1” under each Role. If one role have two or more users, multiple User Names will be marked with “1” under one Role.

200.png

Regards,

@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,

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

2.jpg

 

  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.
    33.png

 

 

Vvelarde
Community Champion
Community Champion

hi @jdormer

 

create this measure:

 

SalesPerson = CALCULATE(VALUES(Users[Name]);FILTER(Managers;Managers[Allocation ID]=1))

 

Project Manager = CALCULATE(VALUES(Users[Name]);FILTER(Managers;Managers[Allocation ID]=2))

 

Architect = CALCULATE(VALUES(Users[Name]);FILTER(Managers;Managers[Allocation ID]=3))

 

image1.png

 




Lima - Peru

Thanks @Vvelarde.

 

When I create the measures you suggest and then add them to a visual, I get a "Can't display the visual." error message. The detailed message states "Calculation error in measure Managers[Architect]: A table of multiple values was supplied where a single value was expected. 

 

 

Thoughts?

Vvelarde
Community Champion
Community Champion

And this happen with the other measures also? or just with Architect?

 

 

 




Lima - Peru

@Vvelarde, I get the same error for all three measures. 

 

EDIT: There are some Engagements that do have duplicate Allocation IDs. 

Vvelarde
Community Champion
Community Champion

Ok, this is a different situation. The posted measures work only when its a unique allocation id by project.

 

Now, with this duplicates rows, how do you want to visualize?




Lima - Peru

@jdormer

 

a Ugly solution is create column calculated for earch allocation:

 

SalesPerson-Col = if(Managers[Allocation ID]=1;RELATED(Users[Name]);BLANK())

 and repeat with others allocation

 

Now put in a matrix visual

image2.png




Lima - Peru

Ideally, I would be able to display the name of both/all salespersons, Project Managers, and Architects in a single row.

 

If you have more than 1 same allocation for a Project then this measure doesn't work.




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.