Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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] ) )
)
)
Solved! Go to Solution.
Hey @v-xinruzhu-msft . 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] )
)
)
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.
Hey @v-xinruzhu-msft . 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] )
)
)
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
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |