Forum Discussion

lnayak's avatar
lnayak
Frequent Visitor
4 years ago
Solved

Use a measure 'Active Unique Headcount' using SUMX function.

HeadCount = VAR selectedDate=
MAX('Calendar'[Date]
RETURN
SUMX('EDE'[Worker #],
VAR WorkerStartDate = ('EDE'[MinStartDate])
VAR WorkerEndDate = ('EDE'[MaxEndDate])
RETURN IF ( WorkerStartDate<=selectedDate && OR (WorkerEndDate>=selectedDate, WorkerEndDate=BLANK(),1,0))
 
Used this function but am not getting the result.
Please help!
 
Thanks in advance
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi lnayak 

    Based on your formula, I created a simple sample.

    Original data :

    Then use the formula you provided . I modified it to return the value 1 or 0 .

    HeadCount =
VAR selectedDate=MAX('Calendar'[Date])
VAR _WorkerStartDate= SELECTEDVALUE(EDE[MinStartDate])
VAR _WorkerEndDate = SELECTEDVALUE(EDE[MaxEndDate])
RETURN IF ( _WorkerStartDate<=selectedDate && _WorkerEndDate>=selectedDate,1,0)

    Then create a measure to count the total value .

    count = SUMX('EDE',[HeadCount])

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regard

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi lnayak 

    Based on your formula, I created a simple sample.

    Original data :

    Then use the formula you provided . I modified it to return the value 1 or 0 .

    HeadCount =
VAR selectedDate=MAX('Calendar'[Date])
VAR _WorkerStartDate= SELECTEDVALUE(EDE[MinStartDate])
VAR _WorkerEndDate = SELECTEDVALUE(EDE[MaxEndDate])
RETURN IF ( _WorkerStartDate<=selectedDate && _WorkerEndDate>=selectedDate,1,0)

    Then create a measure to count the total value .

    count = SUMX('EDE',[HeadCount])

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regard

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi lnayak 

    the first argument of SUMX is a table not a column. Besides no need to store column value in a variable just start with IF directly. But do you have to use SUMX?

    • lnayak's avatar
      lnayak
      Frequent Visitor
      4 years ago

      Hi tamerj1 

       

      Yes, I used table within SUMX. Yes, i want to use this function to know if i get the result.

       

      Thanks,

      Laxmi 

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        You should if there is no other date filter on your visual

  • lnayak's avatar
    lnayak
    Frequent Visitor
    4 years ago

    Hi Tamerj1,

     

    The date filter in report would just be from the calendar.

  • lnayak's avatar
    lnayak
    Frequent Visitor
    4 years ago

    Hi,

     

    Let me explain what am looking for am trying to work on headcount formula and if sumx doesn't work i need to change it to distinct count. can someone help me out here please?