Forum Discussion

apeeltyler's avatar
apeeltyler
Frequent Visitor
2 years ago
Solved

Need help adding to my Measure to count Voluntary/Involuntary Terms

I need some help adding in a column to count Voluntary & Involuntary Terminations to the following code.  I will use this as my lookup to determine who is Voluntary/Involuntary.  I'm not sure where I should add it:

Roster[Vol/Invol]

 

Term EEs Vol/Invol = 
IF (
    CALCULATE (
        COUNT ( Roster[Employee Id] ), 
        USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] ),
        NOT ( ISBLANK ( Roster[Termination Date] ) )
    ) = 0,
    0,
    CALCULATE (
        COUNT ( Roster[Employee Id] ),
        USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] ),
        NOT ( ISBLANK ( Roster[Termination Date] ) )
    )
)

 

 

Need Help
  • apeeltyler's avatar
    apeeltyler
    2 years ago

    Hey Anonymous .  Thanks for replying.  I was able to work with a coworker and we came up with this, using 

    NOT CONTAINSSTRING([Vol/Invol], "Involuntary") &&

      So it ended up with this:  (one for Voluntary and one for Involuntary)

     

    Termed EEs Voluntary = 
IF (
    CALCULATE (
        COUNTROWS (
            FILTER (
                Roster,
                NOT CONTAINSSTRING([Vol/Invol], "Involuntary") &&
                NOT ( ISBLANK ( Roster[Termination Date] ) )
            )
        ),
        USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] )
    ) = 0,
    0,
    CALCULATE (
        COUNTROWS (
            FILTER (
                Roster,
                NOT CONTAINSSTRING([Vol/Invol], "Involuntary") &&
                NOT ( ISBLANK ( Roster[Termination Date] ) )
            )
        ),
        USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] )
    )
)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi apeeltyler 

    You can set it as a paramater

    e.g 

    Term EEs Vol/Invol =
VAR a =
    IF (
        CALCULATE (
            COUNT ( Roster[Employee Id] ),
            USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] ),
            NOT ( ISBLANK ( Roster[Termination Date] ) )
        ) = 0,
        0,
        CALCULATE (
            COUNT ( Roster[Employee Id] ),
            USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] ),
            NOT ( ISBLANK ( Roster[Termination Date] ) )
        )
    )
RETURN
    IF ( a <> 0, [Roster], 0 )

    Best Regards!

    Yolo Zhu

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

    • apeeltyler's avatar
      apeeltyler
      Frequent Visitor
      2 years ago

      Hey Anonymous .  Thanks for replying.  I was able to work with a coworker and we came up with this, using 

      NOT CONTAINSSTRING([Vol/Invol], "Involuntary") &&

        So it ended up with this:  (one for Voluntary and one for Involuntary)

       

      Termed EEs Voluntary = 
IF (
    CALCULATE (
        COUNTROWS (
            FILTER (
                Roster,
                NOT CONTAINSSTRING([Vol/Invol], "Involuntary") &&
                NOT ( ISBLANK ( Roster[Termination Date] ) )
            )
        ),
        USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] )
    ) = 0,
    0,
    CALCULATE (
        COUNTROWS (
            FILTER (
                Roster,
                NOT CONTAINSSTRING([Vol/Invol], "Involuntary") &&
                NOT ( ISBLANK ( Roster[Termination Date] ) )
            )
        ),
        USERELATIONSHIP ( Roster[Termination Date], 'Calendar'[Date] )
    )
)
  • apeeltyler's avatar
    apeeltyler
    Frequent Visitor
    2 years ago

    Forgot to add Roster[Vol/Invol] will either be "Voluntary" or "Involuntary".  I'm just not sure where to add this to my DAX Measure