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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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