How to calculate sum of values by column which values is in another column

[Vitaliy]

Hi !

Please, help with this "simple" task.

I have some source table:

And I want to show result table like this:

In Result table first column (yellow) - users from Source table from column User who log time (also yellow).
I know how to calculate LogTime, but I can't calculate second column - N of unique assets which displays how many tasks the user owns, who logged the time.
How i try to do it:
1) create virtual table via SUMMARIZE and get via LOOKUP needed values. But I can't reference the columns of the virtual table via LOOKUP
2) via FILTER: 

calculate([# of Assets],FILTER(Source table,Source table[User who log time]=Source table[Task owner]))

measure "[# of Assets]" to calculate N of unique assets for task owners =

SUMX(VALUES(Source table[Task ID]),CALCULATE(sum(Source table[N of assets for task])))

But it's not working. 
Please help solve this problem

[Fowmy · Accepted solution]

@Vitaliy 

Add this as a measure:

Unique = 
SUMX(
    SUMMARIZE(
        FILTER(
            ALL('Source table'),
            'Source table'[Task owner] = MAX('Source table'[User who log time])
        ),
        'Source table'[Task ID], "_V", MAX('Source table'[N of assets for task]) ),
    [_V]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[amitchandak]

@Vitaliy , Create a user table and join Task owner and User who log time. One join will be inactive.

Activate the join using use relation.

Log time is based on User who log time join.

Another one is based on ask owner

refer

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...