Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
I was thinking use Power Query to acheive this, but I'm totally clueless. Any help/advice is greatly apprecitaed!
Solved! Go to 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
[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.
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...
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |