cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
graceweii
Regular Visitor

Summarize an ID based on a value in another column

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!

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
daxer-almighty
Solution Sage
Solution Sage

 

 

[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
Solution Sage
Solution Sage

@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
Super User
Super User

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

parry2k
Super User
Super User

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!