Forum Discussion

sridharbabu's avatar
sridharbabu
Helper I
2 years ago

Remove duplicates

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

 

month item location type qty
Apr-245456 london pen44
Mar-245456londonpen 46
Apr-245433london pencil34
Mar-245433londonpen 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-245456 london pen90
Apr-245433london pencil60

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      sridharbabu
      Helper I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.