Measure that groups by two different keys?

[Gjakova]

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

[v-lionel-msft]

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.

[Gjakova]

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:

count(

           case

                   when SickHours>0

                   then 1

                   else 0

            end)

from XXX

group by DateKey, Employee

[v-lionel-msft · Accepted 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.

[amitchandak]

@Gjakova , Not very clear. Are looking for measure segmentation.

refer to my video: https://youtu.be/CuczXPj0N-k