Forum Discussion
Helper IVGet Only One Value from duplicate Values in another column Based on Unique ID
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
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.
9 Replies
- Greg_Deckler
Community Champion
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 __ReturnTechnically, you shouldn't even need that first variable assuming that you put ID and this measure into the same table visualization.
third_hicanaHi 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.206I 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
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 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,
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.
