Forum Discussion

refint650's avatar
refint650
Helper 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
Community 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
    Helper 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] )

     

     

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 years ago

      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.