Remove duplicates

[sridharbabu]

from backend taking two months of data and showing in power bi table

month	item	location	type	qty
Apr-24	5456	london	pen	44
Mar-24	5456	london	pen	46
Apr-24	5433	london	pencil	34
Mar-24	5433	london	pen	26

here if item, location,type is same for two months, sum of qty and show it, if item , location is same and type is different then show the latest moth type, in the table having other columns and measures also.
this is the required output.   how to achieve in power bi dax.

month	item	location	type	qty
Apr-24	5456	london	pen	90
Apr-24	5433	london	pencil	60

[Anonymous]

Your ideas so great @Idrissshatila , Allow me to offer another insight:

Hi, @sridharbabu 

Perhaps you can create a measure using the following DAX expression:

qty measure = 
VAR _date = SELECTEDVALUE('Table'[month])
VAR _item = CALCULATE(MAX('Table'[item]),'Table'[month]=_date)
VAR _location = CALCULATE(MAX('Table'[location]),'Table'[month]=_date)
VAR _type = CALCULATE(MAX('Table'[type]),'Table'[month]=_date)
VAR _max_day = MAXX(ALL('Table'),'Table'[month])
VAR _item_max = CALCULATE(MAX('Table'[item]),'Table'[month]=_max_day)
VAR _location_max = CALCULATE(MAX('Table'[location]),'Table'[month]=_max_day)
VAR _type_max = CALCULATE(MAX('Table'[type]),'Table'[month]=_max_day)
RETURN IF(_item=_item_max&&_location=_location_max&&_type=_type_max&&_date=_max_day,SUMX(FILTER(ALL('Table'),'Table'[item]=_item_max),'Table'[qty]),IF(_date=_max_day,SUMX('Table','Table'[qty])))

The table visual is set as follows:

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[sridharbabu]

Thankyou for the solution. In the table apart from above columns, also having other columns and measures showing in the table. and to show top 100. totally showing 12 columns including measures. for all same process.

[Anonymous]

Hi, @sridharbabu 

Thank you very much for your reply. Has the current issue been resolved? Do you provide some sample data for your description and the output you are looking forward to. If you've already fixed the current issue, you can share your solution here and mark it as a solution so that others in the community can quickly find a solution if they encounter the same problem.

Best Regards

Jianpeng Li

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

[Idrissshatila]

Hello @sridharbabu,

you could acheive the following using group by https://learn.microsoft.com/en-us/power-query/group-by