Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Stan_Lego
Frequent Visitor

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

NameWorkOrderWorkStationCheck in TimeCheck out timeunique declaration
AlphaAK1002/01/2021 14:0002/01/2021 15:301
AlphaAK2004/01/2021 13:3004/01/2021 14;511
BetaBK1002/01/2021 21:0002/01/2021 21:31 
BetaBK1003/01/2021 22:0003/01/2021 22:301
BetaCK2004/01/2021 12:0004/01/2021 13:301

 

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!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 ()
    )

yingyinr_0-1634005832701.png

Best Regards

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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 ()
    )

yingyinr_0-1634005832701.png

Best Regards

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

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.