I can't seem to think of how to calculate this

[Stan_Lego]

Name	WorkOrder	WorkStation	Check in Time	Check out time	unique declaration
Alpha	A	K10	02/01/2021 14:00	02/01/2021 15:30	1
Alpha	A	K20	04/01/2021 13:30	04/01/2021 14;51	1
Beta	B	K10	02/01/2021 21:00	02/01/2021 21:31
Beta	B	K10	03/01/2021 22:00	03/01/2021 22:30	1
Beta	C	K20	04/01/2021 12:00	04/01/2021 13:30	1

So basically i want to count the number of times people check out by unique workstation and unique workorder but I don't know how to approach this issue. Anyone can help me generate the "unique declaration" column? Thanks a lot!

[v-yiruan-msft · Accepted solution]

Hi @Stan_Lego ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure as below to get the unique declaration

unique declaration = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Check out time] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[WorkOrder] = SELECTEDVALUE ( 'Table'[WorkOrder] )
                && 'Table'[WorkStation] = SELECTEDVALUE ( 'Table'[WorkStation] )
        )
    )
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[WorkOrder],
        'Table'[WorkStation],
        "@countofws", CALCULATE ( DISTINCTCOUNT ( 'Table'[WorkStation] ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Check out time] ) = _maxdate,
        SUMX ( _tab, [@countofws] ),
        BLANK ()
    )

Best Regards

[Stan_Lego]

Hi @v-yiruan-msft Thanks a lot for the help. It works perfectly!