DAX to remove rows that are similar but comparing different columns/rows

[databot_kd]

Hi Community, 

hoping you can assist please.

I am trying to remove rows from my table using DAX based on what are considered as duplicate. 

For example from the screenshot below APL to GLG and GLG to APL is the same transfer i need it to only show one row. 

Any help would be appreciated

Group To	Group From	Transfer Amount
APL	GLG	1000
GLG	APL	1000
GLG	PCH	450
PCH	GLG	450
PLS	KLM	360
KLM	PLS	360

[databot_kd · Accepted solution]

Hi community, 

hoping you can assist. I am trying to create a variance to show the balance between inter related entities. 

Please see below of my current data and my required output
I have provided any updated screenshot as this hopefully will clarify the current data output and issue. 

Thanks
@Ahmed 
@Jihwan_Kim 

[databot_kd · Accepted solution]

Hi community, 

hoping you can assist. I am trying to create a variance to show the balance between inter related entities. 

Please see below of my current data and my required output
I have provided any updated screenshot as this hopefully will clarify the current data output and issue. 

Thanks
@Ahmed 
@Jihwan_Kim 

[v-jiewu-msft · Accepted solution]

Hi @databot_kd ,

If I understand correctly, the issue is that you want to remove rows. Please try the following methods and check if they can solve your problem:

1.Create the simple table. 

2.Create a calculated column that generate a unique identifier for each transfer.

Column = 
VAR GroupFrom = [FromBiz]
VAR GroupTo = [ToBiz]
VAR MinGroup = MIN(GroupFrom, GroupTo)
VAR MaxGroup = MAX(GroupFrom, GroupTo)
RETURN
CONCATENATE(MinGroup, MaxGroup)

3.Create a new table Table 2 that removes duplicates based on the column.

Table 2 = 
SUMMARIZE(
    'Table',
    'Table'[Column],
    "FromBiz", MINX(FILTER('Table', 'Table'[Column] = EARLIER('Table'[Column])), [FromBiz]),
    "ToBiz", MAXX(FILTER('Table', 'Table'[Column] = EARLIER('Table'[Column])), [ToBiz]),
    "Amount", SUMX(FILTER('Table', 'Table'[Column] = EARLIER('Table'[Column])), [Amount])
)

4.The result is shown below.

Looking forward to your reply.

Best Regards,

Wisdom Wu

[databot_kd]

Thanks @v-jiewu-msft 

Your solution really helped me. Much appreciated,

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.

[databot_kd]

Hi @Jihwan_Kim , thanks for asssiting. 

Tried this had a issues when using EARLIER for the amount as the amount is a measure coming from another table. Apologies i should have mentioned that. 

[amitchandak]

@databot_kd , Create a measure like this use with Group To , Group From in visual

measure =Sumx(summarize(Table, Table[Group To],Table[Group From], Table[Transfer Amount]),Table[Transfer Amount])

[databot_kd]

Hi @amitchandak ,

Thanks for assisting. I tried the measure you provided but i get the same result as before.
Not sure if this is cause the amount is coming from a different table. Would you have any other thoughts on where i am going wrong?