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 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

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • 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.