Need a Power BI DAX for count unique value among the duplicate value rows

[Emranit]

My actual power BI connected with SQL database. Firstly please see the attached both file. Bulk_production value is related with Dyelot no. There are many duplicate dyelot no. When summation of bulk_production value with duplicate value that is more from actual value.

Need a DAX, if dyelot no duplicate, here will be count one dyelot no and count asociated bulk_production value.

https://drive.google.com/file/d/1bApfixcHSuPBGXH8ieJo5QU88CIjiTrj/view?usp=sharing 

https://docs.google.com/spreadsheets/d/1valTFqJ3OU2IuiG_ErS0bM9k_QCfBFN4/edit?usp=sharing&ouid=11391...

Duplicate dyelot no marked with a red color, if dyelot no rows have duplicate no then bulk_production count one value and other dyelot no's bulk_production value will be 0. Like as Dyelot no 253126042 is double. Here are two bulk_production value one is 312 & another is 312. So, one will be 312 and another will be 0. Or hide the column of Dyelot no, Bulk_production and end date. Thus, all will be same when you get duplicate dyelot no.  Dyelot no 253126042 column will be unique and count one time.

[danextian]

Hi @Emranit 

I'm hoping  I understood your problem correctly, if you want to keep just the bulk product for the latest endtime, you can use SUMX on a virtual table that returns the row with the latest endtime and sum them up. These two measures will return the same total and subtotals but different results and the endtime row level.

BulkProduct Latest Entry CALCULATE = 
SUMX (
    -- Summarize the filtered table
    SUMMARIZE (
        -- Filter for only the latest EndTime per Production Dyelot
        FILTER (
            Duplicate,
            Duplicate[EndTime]
                = CALCULATE (
                    -- Find the maximum EndTime, ignoring other filters except Production Dyelot
                    MAX ( Duplicate[EndTime] ),
                    ALLEXCEPT ( Duplicate, Duplicate[Production Dyelot] )
                )
        ),
        -- Group by Production Dyelot and Bulk_Production
        Duplicate[Production Dyelot],
        Duplicate[Bulk_Production]
    ),
    -- Sum the Bulk_Production values
    [Bulk_Production]
)

BulkProduct Latest Entry MAXX = 
SUMX (
    -- Iterate through the summarized table and sum the Bulk_Production values.
    SUMMARIZE (
        -- Summarize the table to group by Production Dyelot and filter only rows with the latest EndTime.
        FILTER (
            Duplicate,
            -- Keep only rows where the EndTime is the latest for each Production Dyelot.
            Duplicate[EndTime]
                = MAXX (
                    FILTER (
                        Duplicate,
                        Duplicate[Production Dyelot] = EARLIER ( Duplicate[Production Dyelot] )
                    ),
                    Duplicate[EndTime]
                )
        ),
        -- Define the grouping columns: Production Dyelot and Bulk_Production.
        Duplicate[Production Dyelot],
        Duplicate[Bulk_Production]
    ),
    -- Specify the column to sum: Bulk_Production.
    [Bulk_Production]
)

However, if you don't need the duplicate records you follow @Ritaf1983's suggestion.

[Ritaf1983 · Accepted solution]

Hi @Emranit 
To achieve the desired result if you need these duplicates and can't just remove them from the data table, you can give them an index by id from PQ :

I used the method from the linked blog post :
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

Then you can summarize the only rows with index = 1

Sum_bulk production = CALCULATE(sum('Table_Rita'[Bulk_Production]),Table_Rita[Index]=1)

The pbix is attached

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