Unique Employee does set operator function works.?

[refint650]

Hello ALL

couldnt figure how to write this conditional expression  where filter unique employees when purchase flag ='N' and dont  count same employee  if he has flag "Y"

can write set operator calculation does it work

Var A = filter and count employe where purchase flag  ='Y'

Var B =filter and count employe where purchase flag  ='N'

return 

Ps

[refint650]

@Samarth_18 

Appreciate for response @Samarth_18 , In measure what will be return syntax..

[Samarth_18]

@refint650 Its depend on what output you need 🙂

[Samarth_18]

Hi @refint650 ,

Create a column like below:-

Y Flag =
IF (
    CALCULATE (
        COUNT ( 'Table'[EmployeeID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] )
                && 'Table'[Purchase Flag] = "Y"
        )
    ) > 0,
    1,
    0
)

Now create a measure which you trying like below:-

Measure =
VAR A =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[EmployeeID] ),
        FILTER ( 'Table', 'Table'[Y Flag] = 1 )
    )
VAR B =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[EmployeeID] ),
        FILTER ( 'Table', 'Table'[Y Flag] = 0 )
    )

Return

Thanks,

Samarth

[refint650]

EARLIER/EARLIEST refers to an earlier row context which doesn't exist. when doing calculated colum1 

-

'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] )

[v-kkf-msft]

Hi @refint650 ,

Please try this measure. Since I don't know the result you want to return, here I wrote it as "Y_count - N_count". You can change the part of RETURN, or you can split this formula into two and return the counts of Y flag and N flag respectively.

Measure = 
VAR Y_tab =
    CALCULATETABLE ( VALUES ( 'Table'[EmployeeName] ), 'Table'[PurchaseFlag] = "Y" )
VAR Y_count =
    COUNTROWS ( Y_tab )
VAR N_count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[EmployeeName] ),
        FILTER (
            EXCEPT ( VALUES ( 'Table'[EmployeeName] ), Y_tab ),
            MAX ( 'Table'[PurchaseFlag] ) = "N"
        )
    )
RETURN
    Y_count - N_count

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.