Summarize an ID based on a value in another column

[graceweii]

I'm trying to use Power BI Desktop to build a report from a laboratory dataset that records whether a certain sample has been approved or rejected. The dataset has four columns. 'sample id' is the primary key of the dataset(uniquely identify each record in the table). 'Batch id" records the batch that each sample belongs to, and one batch can have multiple samples.  'Create date' is the date that the sample was created. 'Review state" records the final result of each sample. 

Batch id	Sample id	Create date	Review state
1	a	6/1	rejected
1	b	6/1	rejected
2	c	6/1	approved
3	d	6/1	approved
4	e	6/1	rejected
4	f	6/5	approved
5	g	6/1	rejected
6	h	6/1	approved
6	i	6/1	approved

However, I want to create a table that summarizes on the batch id. And will contain the review state for each batch. The final table should look like this: 

Batch id	Review state
1	rejected
2	approved
3	approved
4	approved
5	rejected
6	approved

The review state for each batch is determined by the following conditions: 

1. If two samples belong to the same batch, but the review state is different:  keep the review state that has a later date. Example: sample e and sample f are from the same batch, but have different review states. Keep the one that has a later create date, which is sample f. Then batch 4 will have the review state "approved".
2. If two samples belong to the same batch, and the review states are the same: keep the review state as the same. Example: sample a and sample b have the same state: rejected. Keep batch1's review state as rejected.
3. If a batch only has one sample, then keep the reivew state. Example: batch 5 only has sample g that is rejected, so batch 5 will be rejected on the final table. 

I was thinking use Power Query to acheive this, but I'm totally clueless. Any help/advice is greatly apprecitaed!

[daxer-almighty]

[Summary Table] =
var BatchWithMaxDate =
    ADDCOLUMNS(
        VALUES ( T[Batch ID] ),
        "Review Date",
            CALCULATE( MAX( T[Create Date] ) )
    )
var Result =
    selectcolumns(
        calculatetable(
            T,
            treatas(
                BatchWithMaxDate,
                T[Batch ID],
                T[Create Date]
            )
        ),
        "Batch ID", T[Batch ID],
        "Review State", T[Review State]
)
return
    Result

This is the correct way to do it. On big sets it'll be much faster than the solution given by @parry2k.

[daxer-almighty]

@graceweii 

Do you want to create a calculated table in PBI? Or you want to summarize the data in Power Query and then load this summary table into the model? Because it does not sound like you want to create a measure of any kind...

[parry2k · Accepted solution]

@graceweii if you really need to create a table, you can create a table using the following DAX statement, do add a measure that I gave you in the previous reply

Batch = SUMMARIZE ( 'Sample', 'Sample'[Batch id], "Review State", [Measure] ) 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@graceweii you can create the following measure and put it in a table visual, batch id and the following measure and you will get the output:

Measure = 
CALCULATE ( 
    MAX ( 'Sample'[Review state] ), 
    TOPN  ( 
        1, 
        ALLEXCEPT ( 'Sample', 'Sample'[Batch id] ), 
        CALCULATE ( MAX ( 'Sample'[Create date] ) ), 
        DESC 
    ) 
) 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡