cancel
Showing results for
Did you mean:

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

Frequent Visitor

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

 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!

1 ACCEPTED SOLUTION
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 ()
)``````

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.
2 REPLIES 2
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 ()
)``````

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.
Frequent Visitor

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors