cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Mark an id# based on if times are duplicates for certain parts from another column

Data looks like this:

DispatchID      Part     Created Date
1234                M         1/1/20 04:34:10 PM
1234                L          1/1/20 04:34:10 PM
1234                T          1/1/20 04:34:10 PM
1234                R          1/5/20 05:15:59 PM
1313                M         1/1/20 06:33:10 PM
1313                L           1/1/20 06:33:10 PM
1313                T           1/2/20 08:44:18 PM
2424               M           1/4/20 10:11:22 PM
2424               L             1/4/20 10:11:22 PM
2424               T             1/4/20 10:11:22 PM
2424               Q            1/7/20 08:10:40 PM

I want to find when all the parts for any DispatchID have the exact same times for certain parts. Below is the result i want to see if only considering parts - M,L,T:

DispatchID     Same Time Result

1234               yes
1313               no
2424               yes

Please assist with a measure I that would work.

1 ACCEPTED SOLUTION
Community Champion

with your sample data I get the following solution:

``````Measure =
VAR _Part_M = CALCULATE(MIN('Table'[Created Date]), 'Table'[Part] = "M")
VAR _Part_L = CALCULATE(MIN('Table'[Created Date]), 'Table'[Part] = "L")
VAR _Part_T = CALCULATE(MIN('Table'[Created Date]), 'Table'[Part] = "T")
RETURN
IF(_Part_L = _Part_M && _Part_M = _Part_T, "Yes", "No")``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

7 REPLIES 7
Community Support

Hi @sfsamperi13 ,

If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please give us more details.

Best Regards,

Icey

Microsoft Employee

@sfsamperi13 I don't understand your calculation. 1313 also has exactly same date as below then Why the result is "No"

1313                M         1/1/20 06:33:10 PM
1313                L           1/1/20 06:33:10 PM

Microsoft Employee

Microsoft Employee

Looks like I misread your question. Anyways you have the solutions 🙂

Regular Visitor

1313 is no because T does not have the same time as M and L. All 3 parts need to have the same time.

Super User

Give this a try.

``````Same Time Result =
IF ( DISTINCTCOUNT ( YourTable[Created Date] ) = 1, "yes", "no" )``````

Community Champion

with your sample data I get the following solution:

``````Measure =
VAR _Part_M = CALCULATE(MIN('Table'[Created Date]), 'Table'[Part] = "M")
VAR _Part_L = CALCULATE(MIN('Table'[Created Date]), 'Table'[Part] = "L")
VAR _Part_T = CALCULATE(MIN('Table'[Created Date]), 'Table'[Part] = "T")
RETURN
IF(_Part_L = _Part_M && _Part_M = _Part_T, "Yes", "No")``````

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.