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

[third_hicana]

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

[v-tangjie-msft]

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. 

[third_hicana]

Hi @v-tangjie-msft 

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,

[v-tangjie-msft · Accepted solution]

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. 

[third_hicana]

@v-tangjie-msft  Thank you very much for your help. This works well. 

[Greg_Deckler]

@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.

[third_hicana]

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

[Greg_Deckler]

@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

[third_hicana]

Hi @Greg_Deckler 

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

[Greg_Deckler]

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