How to find the column according to the minimum value and the position

[Money]

Hi Experts,

Hope everyone doing good !

A new column should be added as a function of the condition.

Match Condition 

eg : ID 1877

1.) In the id group, the minimum value of tho_cw_diff should be checked and Rail position of thor_pos/CW_pos should be same.

eg : ID 7931

2.) If the rail position is not the same for the minimum tho_cw_diff value, check for the other minimum value in group ID that the rail position thor_pos/CW_po is the same.

eg: ID 8880

3.) If all the rail position is different for the group ID, select the minimum tho_cw_diff value.

According to these three conditions, the new column must be updated.

Sharing screenshot data of the expected outputs below

@Greg_Deckler @amitchandak @FreemanZ @Mikelytics @ppm1 

[v-zhangti]

Hi, @Money 

You can try the following methods.
Column:

MIN Diff = 
Var _Min1=
CALCULATE(MIN('Table'[THO_CW_DIFF]),FILTER('Table',[ID]=EARLIER('Table'[ID])&&[THO_position]=[CW_POS]))
Return
IF(_Min1=BLANK(),CALCULATE(MIN('Table'[THO_CW_DIFF]),FILTER('Table',[ID]=EARLIER('Table'[ID]))),_Min1)

Result = IF([THO_CW_DIFF]=[MIN Diff],"Yes","No")

Is this the result you expect? At ID 8880, -0.034 is smaller, isn't it?

Best Regards,

Community Support Team _Charlotte

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

[Money]

@v-zhangti Hi Team,

Thanks for guiding and helping 

302.60 - 302.627 = -0.27 is the smaller when compare to the last group ID  : 8880 

and for the sample data we are getting the desired output but for the actual data the result is diffrent for the other group ids 

Attached the result from the actual orginal data. 

Please do the needful 🙂 

[Money]

@v-zhangti 

302.60 - 302.627 = -0.27 is the smaller when compare to the last group ID  : 8880 

We are finding the close CW_MP 

How to update the above shared calculated column to get this result. 

[tamerj1]

Hi @Money 

please try

=
VAR CurrentValue = 'Table'[tho_cw_diff]
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) )
VAR FiteredTable =
FILTER ( CurrentIDTable, 'Table'[THO_position] = 'Table'[CW_POS] )
VAR ReturnValue =
IF (
ISEMPTY ( FiteredTable ),
MINX ( CurrentIDTable, 'Table'[tho_cw_diff] ),
MINX ( FiteredTable, 'Table'[tho_cw_diff] )
)
RETURN
IF ( CurrentValue = ReturnValue, "Yes", "No" )

[Money]

@tamerj1 Sir

I have tried the above calculated column, but unable to get the expected output. 

The given answer is working for the first set of group ID : 1877. For other group of id its getting randomly updating as "yes"or "no". Could you please guide me sir 

[tamerj1]

@Money 
Please try

=
VAR CurrentValue = 'Table'[tho_cw_diff]
VAR CurrentIDTable =
    CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[THO_MP] ) )
VAR FiteredTable =
    FILTER ( CurrentIDTable, 'Table'[THO_position] = 'Table'[CW_POS] )
VAR ReturnValue =
    IF (
        ISEMPTY ( FiteredTable ),
        MINX ( CurrentIDTable, 'Table'[tho_cw_diff] ),
        MINX ( FiteredTable, 'Table'[tho_cw_diff] )
    )
RETURN
    IF ( CurrentValue = ReturnValue, "Yes", "No" )

[Money]

@tamerj1 Sir

Getting "No" for all the ID's  after updated the answer.

[tamerj1]

@Money 

How do you define a group?

[Money]

@tamerj1 Sir 

The group is ID having same number more than one count or single count. 

Eg:

group 1 (having have 4 ID )

1551877

1551877

1551877

1551877

group 2 (having have 2 ID )

1551875

1551875

group 3 (having have 2 ID )

1551841

1551841

group 4 (having have 1 ID )

1551614

group 5 (having have 1 ID )

1551613

group 6 (having have 1 ID )

1551612

If ID having more than one count then we are doing matching based on the condtion to select only one ID from that Group . If the ID count is single then we can directly mark it as "YES" (For the above shared eg : Group 4, Group5, Group 6)

[tamerj1]

@Money 
Is this the correct result? If not would you please highight wrong results and explain why. Thank you.

[Money]

@tamerj1 Sir how to modify the calculated column to get the shared image result.

=
VAR CurrentValue = 'Table'[tho_cw_diff]
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) )
VAR FiteredTable =
FILTER ( CurrentIDTable, 'Table'[THO_position] = 'Table'[CW_POS] )
VAR ReturnValue =
IF (
ISEMPTY ( FiteredTable ),
MINX ( CurrentIDTable, 'Table'[tho_cw_diff] ),
MINX ( FiteredTable, 'Table'[tho_cw_diff] )
)
RETURN
IF ( CurrentValue = ReturnValue, "Yes", "No" )

We are tryping to get the closet CW_Mp  

Eg : THO_MP = 173 is very close to CW_MP = 173.072 

Sorry for the update 😞 

Thank you 

[tamerj1]

@Money 

Please try

=
VAR CurrentValue =
ABS ( 'Table'[tho_cw_diff] )
VAR CurrentIDTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[THO_MP] ) )
VAR FiteredTable =
FILTER ( CurrentIDTable, 'Table'[THO_position] = 'Table'[CW_POS] )
VAR ReturnValue =
IF (
ISEMPTY ( FiteredTable ),
MINX ( CurrentIDTable, ABS ( 'Table'[tho_cw_diff] ) ),
MINX ( FiteredTable, ABS ( 'Table'[tho_cw_diff] ) )
)
RETURN
IF ( CurrentValue = ReturnValue, "Yes", "No" )

[v-zhangti]

Hi, @Money 

You can try the following methods.

ABS DIFF = ABS([THO_CW_DIFF])

MIN Diff = 
Var _Min1=
CALCULATE(MIN('Table'[ABS DIFF]),FILTER('Table',[ID]=EARLIER('Table'[ID])&&[THO_position]=[CW_POS]))
Return
IF(_Min1=BLANK(),CALCULATE(MIN('Table'[ABS DIFF]),FILTER('Table',[ID]=EARLIER('Table'[ID]))),_Min1)

Result = IF([ABS DIFF]=[MIN Diff],"Yes","No")

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Money]

Hi Sir/Madam @v-zhangti  above calculation is working good for that scenario . Another validation is added into the logic. below sharing the screenshot and the new validation. Whether we can add the new validation in the given calculation. 

Red box are duplicates CWR ID. and duplicates should not come. once the one CWR ID is matched with Thor ID then we should not check that CWR ID again. 

[Money]

@tamerj1 Sir 

This is the expected and correct result Sir. All Good

[tamerj1]

@Money 

This is my very first solution 😅

[Money]

@tamerj1 Sir

Same solution I'm using but getting different result from my end. What could be the reason? 

I'm using 2 different databases which are databricks and IBM DB2 and merging queries by the inner join. Then making this calculation. because of that it causing any discrepancy ? 

[tamerj1]

@Money 

How are you dealing with the negative values?

[Money]

@tamerj1   Hi Sir ,

We have to consider the minimum diffrence for both the positive as well negative value.

302.60 - 302.627 = -0.27 is the smaller when compare to the last group ID  : 8880 

Attached the result from the actual orginal data. 

Please 

[Money]

@amitchandak  sir 

We have to consider the Thor position and CW pos column for the match based on the 3 conditon.

Above shared example unable to get the expected new column result.

please guide me to get the desired output sir. 

Thanks in adavnce 

highlighted the position