topic Re: How to find the column according to the minimum value and the position in DAX Commands and Tips

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

Money — Fri, 03 Feb 2023 22:17:00 GMT

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

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

amitchandak — Sat, 04 Feb 2023 02:10:52 GMT

Money ,

New column =

var _min = minx(filter(Table, [ID] = earlier([ID]) ), [tho_cw_diff])

return

if([tho_cw_diff] = _min, "Yes", "No")

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

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

Money — Sat, 04 Feb 2023 06:01:39 GMT

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

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

tamerj1 — Sat, 04 Feb 2023 12:40:17 GMT

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" )

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

v-zhangti — Mon, 06 Feb 2023 05:52:17 GMT

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.

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

Money — Mon, 06 Feb 2023 06:27:04 GMT

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

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

tamerj1 — Mon, 06 Feb 2023 07:23:53 GMT

Money

How are you dealing with the negative values?

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

Money — Mon, 06 Feb 2023 14:45:58 GMT

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

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

Money — Mon, 06 Feb 2023 14:51:31 GMT

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 🙂

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

tamerj1 — Mon, 06 Feb 2023 14:55:44 GMT

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" )

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

Money — Mon, 06 Feb 2023 15:06:44 GMT

tamerj1 Sir

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

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

tamerj1 — Mon, 06 Feb 2023 15:34:37 GMT

Money

How do you define a group?

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

Money — Mon, 06 Feb 2023 15:47:13 GMT

tamerj1 Sir

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

Eg:

group 1 (having have 4 ID )

1551877

group 2 (having have 2 ID )

1551875

group 3 (having have 2 ID )

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)

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

tamerj1 — Mon, 06 Feb 2023 17:02:06 GMT

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

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

Money — Mon, 06 Feb 2023 18:36:42 GMT

tamerj1 Sir

This is the expected and correct result Sir. All Good

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

Money — Thu, 23 Feb 2023 15:29:37 GMT

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.

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

tamerj1 — Mon, 06 Feb 2023 18:48:41 GMT

Money

This is my very first solution 😅

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

Money — Mon, 06 Feb 2023 20:25:16 GMT

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 ?

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

Money — Thu, 23 Feb 2023 15:25:39 GMT

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

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

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

tamerj1 — Thu, 23 Feb 2023 15:53:47 GMT

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" )