Measure or column to check if user has more than 1 task assigned or more than 1 task completion

[Chris2016]

Hello,

I need help with the following issue. I have a table like this:

Name	Task	Active Task	Active User	Assigned Date	Completed date	CompletionPercentage
A	no1	True	True	11/09/2022	10/10/2022	100
A	no3	False	True	01/02/2020	02/04/2020	100
A	no2	True	True	05/10/2022	05/11/2022	100
B	no1	True	True	05/02/2022	06/10/2022	100
B	no2	True	True	04/07/2022		54.32098765
C	no2	True	True	10/12/2021	10/01/2022	100
C	no1	True	True	04/07/2022	04/08/2022	100
D	no2	True	True	02/15/2022	04/10/2022	100
D		True	True	04/03/2022		92.30769231
F	no1	True	True	05/10/2022		1.666666667
G	no2	True	True			53.08641975
H	no1	True	True		10/05/2022	100
I	no4	True	True
J	no4	True	True		11/05/2021	100
K	no3	False	False

I am trying to calculate if there are users who have more than 1 task assigned and more than 1 task completion:

Users with more than 1 task = If(CALCULATE(DISTINCTCOUNT('Table'[Task]),DISTINCT('Table'[Name]), 
'Table'[Active Task]=TRUE(), 'Table'[Active User]= TRUE(), 
USERELATIONSHIP('Table'[Assigned Date], 'Calendar'[Date]))>1, 1)

Users with more than 1 completion = If(CALCULATE(DISTINCTCOUNT('Table'[Task]), 
DISTINCT('Table'[Name]),'Table'[Active Task] = TRUE(),'Table'[Active User] = TRUE (), 'Table'[CompletionPercentage]=100,  
USERELATIONSHIP('Table'[Completed date], 'Calendar'[Date]))>1,1)

With these measures however I am not able to get aggregates if I need to view, for example, by fiscal year:

Is there any way to get the sums of users who have multiple tasks and completions?

Thanks a lot for any help!

[Chris2016 · Accepted solution]

I am using this measure to get what I need:

Users with more than 1 completion = 

VAR Tbl =

    ADDCOLUMNS (

        SUMMARIZE ( 'Table', 'Table'[Name] ),

        "Total_Task", 

            CALCULATE (

                DISTINCTCOUNT ( 'Table'[Task] ),

                'Table'[Active Task] = TRUE (),

                'Table'[Active User] = TRUE (),

                'Table'[CompletionPercentage] = 100,

                USERELATIONSHIP ( 'Table'[Completed date], 'Calendar'[Date] )

            )

    )

RETURN

    COUNTX ( FILTER ( Tbl, [Total_Task] > 1 ), [Name] )

[tamerj1]

Hi @Chris2016 
Please try

Users with more than 1 task =
SUMX (
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Name], 'Calendar'[FY] ),
        USERELATIONSHIP ( 'Table'[Assigned Date], 'Calendar'[Date] )
    ),
    IF (
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Task] ),
            DISTINCT ( 'Table'[Name] ),
            'Table'[Active Task] = TRUE (),
            'Table'[Active User] = TRUE (),
            USERELATIONSHIP ( 'Table'[Assigned Date], 'Calendar'[Date] )
        ) > 1,
        1
    )
)

[Chris2016]

Hi, @tamerj1 ,

Thanks a lot for your reply.

I used your formula, but it is not giving the correct sums or total:

As you can see in the sample table, there are 4 users who have more than 1 task assigned and 2 users who have completed more than 1 task.

Thanks!