topic Re: Get Only One Value from duplicate Values in another column Based on Unique ID in DAX Commands and Tips

Get Only One Value from duplicate Values in another column Based on Unique ID

third_hicana — Sun, 15 Sep 2024 14:12:06 GMT

Hi Everyone. Asking for your help in creating DAX

I would like to get the value in another column based on unique id. The value that I want ot get has the same values in another rows due to multiple criteria.

In my example below. I want to get only the 67.206 in refined_value column with unique ID of BOP23-016 and money_type of "Total" under "Refined BOP". The reason that the TOTAL 67.206 is duplicated is because of the sub ids (projects) in ID_&_Suffix column which are under the unique ID BOP23-016. I don't want to add the 67.206 "TOTAL" of "Refined BOP" with sub IDs BOP23-016-B and BOP23-016-C. I want to just appear the value once because they have unique ID (BOP23-016)

I tried to search for solutions in the community but got no luck.

Thank you in advance and I highly appreciate your help.

-Third

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

Greg_Deckler — Sun, 15 Sep 2024 15:55:33 GMT

third_hicana Should be something like this as a measure:

Measure = VAR __ID = MAX( 'Table'[ID] ) VAR __Return = MAXX( FILTER( 'Table', [ID] = __ID && [money_type] = "TOTAL" ), [refined_value] ) RETURN __Return

Technically, you shouldn't even need that first variable assuming that you put ID and this measure into the same table visualization.

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

third_hicana — Mon, 16 Sep 2024 03:24:35 GMT

Hi Greg_Deckler

Thank you for your response. I notice that it onlty applies to one unique ID. However, if I have another Unique ID with the same data scenario, it does not add up the values under "TOTAL" and "Refined BOP". So, I want to sum the 67.206 and 65 = 132.206

I tried to remove the first variable, but it has the same result.

-Third

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

Greg_Deckler — Mon, 16 Sep 2024 13:21:24 GMT

third_hicana You can do something like this:

Measure = VAR __Table = ADDCOLUMNS( DISTINCT( 'Table'[ID] ), "__Value", VAR __ID = [ID] VAR __Return = MAXX( FILTER( 'Table', [ID] = __ID && [money_type] = "TOTAL" ), [refined_value] ) RETURN __Return ) VAR __Return = SUMX( __Table, [__Value] ) RETURN __Return

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

third_hicana — Mon, 16 Sep 2024 14:48:09 GMT

Hi Greg_Deckler

I tried your revised DAX, but I am still getting a wrong sum of values.

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

Anonymous — Tue, 17 Sep 2024 07:19:55 GMT

Hi third_hicana ,

First of all, thank you Greg_Deckler for your interest in this thread. I have some other thoughts to add:

We can create a measure.

Measure = var _table=FILTER(SUMMARIZE(ALL('Table'),[ID],[money_type],[projects_tagging],[refined_value]),[money_type]="TOTAL" && [projects_tagging]="Refined BOP") RETURN SUMX(_table,[refined_value])

If I have misunderstood you, please provide your example data and expected output.

Best Regards,

Neeko Tang

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

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

Greg_Deckler — Tue, 17 Sep 2024 13:43:33 GMT

third_hicana Post sample data as text and expected results. Then I can create a PBIX for it.

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

third_hicana — Tue, 17 Sep 2024 14:25:47 GMT

Hi Anonymous

Thank you for your reply.

This is good. However, when I tried to put a slicer to check if the measure is dyamic, it doesn't change based on the ID I've selected. So for example, if I select ID BOP23-016, I am expecting that the value is 67.21,

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

Anonymous — Wed, 18 Sep 2024 01:26:36 GMT

Hi third_hicana ,

Update the measure.

Measure = var _table=FILTER(SUMMARIZE(ALLSELECTED('Table'),[ID],[money_type],[projects_tagging],[refined_value]),[money_type]="TOTAL" && [projects_tagging]="Refined BOP") RETURN SUMX(_table,[refined_value])

Best Regards,

Neeko Tang

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

Re: Get Only One Value from duplicate Values in another column Based on Unique ID

third_hicana — Thu, 19 Sep 2024 10:19:28 GMT

Anonymous Thank you very much for your help. This works well.