Forum Discussion

refint650's avatar
refint650
Icon for Helper III rankHelper III
4 years ago

Unique Employee does set operator function works.?

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 ...
Need Help
Show and Tell
Tips and Tricks
Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
refint650
Icon for Helper III rankHelper III
4 years ago

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

-

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