Problems with duplicate data processing after a separate column

[DataAnalysis]

Hello.
I use PowerBI in Korea.
Recently, I've been doing a lot of different things since I started using PowerBI.
There is a recent issue, and I am writing here to ask for your advice.

There is data separated by , (Comma), and I used the Split Column function to visualize each of them.
Since using Split Column, duplicate data has been generated for working hours, causing problems with overall statistics. (See Excel below)
I want one of the two things.

Please refer to Case1 and Case2 in the figure.

1. Enter Working hour data divided by the number of columns after Split Column.

2. After Split Column, let Working Hour Data enter only the top one. Other than that, enter 0 or Null.

Any of the two cases is fine.

I need your advice.

※ I am sorry that I have to upload Excel example instead of PowerBI file due to internal data of the company.

[ribisht17]

You can download the PBIX from here PowerBI Community - Google Drive

[DataAnalysis]

Thank you very much.
I answered Solved first, but can I ask you an additional question?
Thanks to your dedication, heat is generated without errors. When I enter your Formula in New Column in Column Tool, Column is created!!

However, one other error occurs.

"DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."
Case number is the Text type.

In the case of Active Hours, it is the Whole Number Type.

For MPC, the text type is not Number.
Since there are more than 10 locations, hexadecimal numbers A to F were also used to distinguish. Therefore, conversion to Whole Number is not possible...
If there is a problem, will it happen here?

Therefore, the following error occurs. What should I do?

When I converted the Actual hours to Text, all duplicate values were printed as they were.

[DataAnalysis]

I tried this and that in my own way, but suddenly I got a good output value.

Thank you so much!
It's thanks to you. I was really surprised by your technical skills.

[ribisht17]

Glad that it helped you, Cheers 🙂

[ribisht17 · Accepted solution]

Please make use of Column Tool to create this formula instead

Custom Case2 = IF( ( RANKX (

FILTER (

'community_assist_duplicate (2)',

'community_assist_duplicate (2)'[Working Number]

= EARLIER ( 'community_assist_duplicate (2)'[Working Number] )

),

'community_assist_duplicate (2)'[Working PLace],

,

ASC,

DENSE

)) =1,'community_assist_duplicate (2)'[Working Hours],"0")

Regards,

Ritesh 

"Please mark the answer if it helps you so that it can help others as well"

[ribisht17]

Please find my refined DAX

Final Solution 2 = IF( ( RANKX (

FILTER (

'community_assist_duplicate (2)',

'community_assist_duplicate (2)'[Working Number]

= EARLIER ( 'community_assist_duplicate (2)'[Working Number] )

),

'community_assist_duplicate (2)'[Working PLace],

,

ASC,

DENSE

)) =1,'community_assist_duplicate (2)'[Working Hours],0)

[ribisht17]

Hi There,

Are you expecting something like this?

Final Nested Rank = IF( RANKX( ALL('community_assist_duplicate (2)'[Working PLace]),'community_assist_duplicate (2)'[Working PLace],,ASC)=1,'community_assist_duplicate (2)'[Working Hours],0)

Regards,

Ritesh

"Please mark the answer if it helps you so that it can help others as well"

[DataAnalysis]

Thank you very much for your dedicated advice.
As you advised, DAX modified the syntax to match my Data Column name.

(Working Number  = CaseNumber

Working Place = MPC

Wroking Hours = ActualHours)

However, grammatical errors continue to occur.
I will also show you my Data column.

Error Name : 

A single value for column 'actualhours' in table 'community_assist_duplicate (2)' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

My PowerBI level is a beginner, so I'm very ashamed.
I'd appreciate it if you could help me a little more.

[ribisht17]

Hey nothing to be ashamed, I am happy to help if I can, this time I think my mistake, please put the 0 within the quotation then you should not get the error, see the bold-one below

SUGGESTION 1: You should make use of your table name where you are in, community_assist_duplicate is my table name

SUGGESTION 2: Keep 0 within quotations

FILTER (

'community_assist_duplicate (2)',

'community_assist_duplicate (2)'[Working Number]

= EARLIER ( 'community_assist_duplicate (2)'[Working Number] )

),

'community_assist_duplicate (2)'[Working PLace],

,

ASC,

DENSE

)) =1,'community_assist_duplicate (2)'[Working Hours],"0")

[DataAnalysis]

Thank you for your advice.
That's right. Case 2 that I want.
I'm still a beginner, so I don't know much about the PowerQuery syntax.
I'm sorry.

As your advice, CustomColumn has executed your syntax on the data inside my company.
However, a token literal expected error occurs.

What is the problem with the syntax I created?