Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there, I have the following problem:
I want a DAX script that makes a measure where is added the # of times SickHours> 0 = 1. So if 1 employee key is selected, for example, it will get the number of unique date keys where SickHours> 0 = 1. And if there for example 1 day is selected, I get the number of unique employee keys where Sick Hours> 0 = 1
DateKey | EmployeeKey | SickHours > 0 |
4 | 1 | 1 |
4 | 2 | 0 |
4 | 3 | 0 |
4 | 1 | 1 |
16 | 1 | 0 |
16 | 2 | 1 |
16 | 3 | 1 |
390 | 1 | 1 |
390 | 3 | 1 |
Solved! Go to Solution.
Hi @Gjakova ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Gjakova ,
Like this?
Measure =
IF(
ISFILTERED(Sheet3[EmployeeKey]),
COUNTX(
FILTER( Sheet3, Sheet3[SickHours > 0] = 1 ),
[DateKey]
),
IF(
ISFILTERED(Sheet3[DateKey]),
COUNTX(
FILTER( Sheet3, Sheet3[SickHours > 0] = 1 ),
[EmployeeKey]
)
)
)
Or like this?
Measure 2 =
COUNTX(
FILTER( Sheet3, Sheet3[DateKey] = MAX(Sheet3[DateKey]) && Sheet3[EmployeeKey] = MAX(Sheet3[EmployeeKey]) && Sheet3[SickHours > 0] = 1 ),
[SickHours > 0]
)
If not, please show me the expected output result.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lionel-msft I think your suggestion will work, I will try it out today. This is my expected output btw:
The SQL statement would look like this:
Hi @Gjakova ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Gjakova , Not very clear. Are looking for measure segmentation.
refer to my video: https://youtu.be/CuczXPj0N-k
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |