Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Power BI Expert,
need an advice for summarizecolumn dax.
Here is the origin table :
and here is the result :
I want to get the Evaluation value of the latest date for each component such as Engine, but when I use summarizecolumn, it seems not all data gets collected in new table. Same problem occurs when I see the Transmission result (not all Evaluation value shown).
However, the other equipments are showing the Evaluation value.
Thank you and really appreciated for the help.
Solved! Go to Solution.
@Irwan
You're right i corrected it :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi @Irwan
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
hello @Ritaf1983
here is the origin data
Sampled Date | Asset ID | Component | Evaluation |
09-Feb-23 | DELLO_CO04 | ENGINE | A |
05-Nov-22 | DELLO_CO05 | ENGINE | A |
30-Sep-22 | DELLO_CO05 | ENGINE | A |
22-Jul-22 | DELLO_CO05 | ENGINE | A |
13-May-22 | DELLO_CO05 | ENGINE | A |
22-Apr-22 | DELLO_CO05 | ENGINE | A |
18-Mar-22 | DELLO_CO05 | ENGINE | A |
24-Feb-22 | DELLO_CO04 | ENGINE | A |
04-Feb-22 | DELLO_CO04 | ENGINE | A |
28-Jan-22 | DELLO_CO05 | ENGINE | A |
17-Feb-23 | DELLO_CO05 | ENGINE | C |
17-Nov-22 | DELLO_CO04 | ENGINE | C |
18-Apr-22 | DELLO_CO04 | ENGINE | C |
29-Oct-21 | DELLO_CO05 | ENGINE | C |
28-Oct-21 | DELLO_CO04 | ENGINE | C |
23-Jun-23 | DELLO_CO04 | ENGINE | X |
10-Aug-23 | DELLO_CO04 | TRANSMISSION | C |
03-Aug-23 | DELLO_CO04 | TRANSMISSION | C |
17-Nov-22 | DELLO_CO04 | TRANSMISSION | C |
01-Sep-23 | DELLO_CO05 | TRANSMISSION | A |
17-Feb-23 | DELLO_CO05 | TRANSMISSION | A |
05-Nov-22 | DELLO_CO05 | TRANSMISSION | A |
22-Jul-22 | DELLO_CO05 | TRANSMISSION | A |
24-Jun-22 | DELLO_CO05 | TRANSMISSION | A |
16-Jun-22 | DELLO_CO04 | TRANSMISSION | A |
13-May-22 | DELLO_CO05 | TRANSMISSION | A |
22-Apr-22 | DELLO_CO05 | TRANSMISSION | A |
18-Mar-22 | DELLO_CO05 | TRANSMISSION | A |
24-Feb-22 | DELLO_CO04 | TRANSMISSION | A |
28-Jan-22 | DELLO_CO05 | TRANSMISSION | A |
28-Oct-21 | DELLO_CO04 | TRANSMISSION | A |
23-Jun-23 | DELLO_CO04 | TRANSMISSION | B |
09-Feb-23 | DELLO_CO04 | TRANSMISSION | B |
29-Dec-22 | DELLO_CO04 | TRANSMISSION | B |
30-Sep-22 | DELLO_CO05 | TRANSMISSION | B |
12-May-22 | DELLO_CO04 | TRANSMISSION | B |
18-Apr-22 | DELLO_CO04 | TRANSMISSION | B |
04-Feb-22 | DELLO_CO04 | TRANSMISSION | B |
10-Dec-21 | DELLO_CO05 | TRANSMISSION | B |
25-Nov-21 | DELLO_CO04 | TRANSMISSION | B |
29-Oct-21 | DELLO_CO05 | TRANSMISSION | B |
01-Sep-23 | DELLO_CO05 | ENGINE | B |
03-Aug-23 | DELLO_CO04 | ENGINE | B |
29-Dec-22 | DELLO_CO04 | ENGINE | B |
24-Jun-22 | DELLO_CO05 | ENGINE | B |
16-Jun-22 | DELLO_CO04 | ENGINE | B |
12-May-22 | DELLO_CO04 | ENGINE | B |
10-Dec-21 | DELLO_CO05 | ENGINE | B |
25-Nov-21 | DELLO_CO04 | ENGINE | B |
Expected result in New Table :
Sampled Date | Asset ID | Component | Evaluation |
1-Sep-23 | DELLO_CO05 | TRANSMISSION | A |
1-Sep-23 | DELLO_CO05 | ENGINE | B |
10-Aug-23 | DELLO_CO04 | TRANSMISSION | C |
3-Aug-23 | DELLO_CO04 | ENGINE | B |
I am trying to get the Evaluation value for the latest Sample Date for each component.
Thank you and really appreciated for the help
Hi @Irwan
please try this dax :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hello @Ritaf1983 ,
Yes it is pulling the Evaluation value, but the value is wrong.
For example, the Evaluation for "Engine" DELLO_CO04 on 3 August 2023 is supposed to be "B" but the result table shows "X".
I got the same result as well. Any idea why the value is different? (seems it is pulling Evaluation in wrong date i guess).
Thank you and appreciated for the help.
Different result value in new table:
Value on original table:
@Irwan
You're right i corrected it :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hello @Ritaf1983
the formula works wonderfully. The value in new table is matched with the original table.
Thank you and really appreciated for the formula.
Glad to help 🙂
Hello @Ritaf1983 ,
1 more question, the result shows Engine and Transmission go into one column, is there a way to make those Component in different column.
The table will be something like this:
Asset ID | Sampled Date1 | Component1 | Evaluation1 | Sampled Date2 | Component2 | Evaluation2 |
DELLO_CO04 | 03/08/2023 | ENGINE | B | 10/08/2023 | TRANSMISSION | C |
DELLO_CO05 | 01/09/2023 | ENGINE | B | 01/09/2023 | TRANSMISSION | A |
Thank you and really appreciated for the help.
Hi @Irwan
sorry I don't understand what is column you need to split
Hello @Ritaf1983
the table will be looking like this. The information of same asset id will be in one row.
Asset ID | Sampled Date1 | Component1 | Evaluation1 | Sampled Date2 | Component2 | Evaluation2 |
DELLO_CO04 | 03/08/2023 | ENGINE | B | 10/08/2023 | TRANSMISSION | C |
DELLO_CO05 | 01/09/2023 | ENGINE | B | 01/09/2023 | TRANSMISSION | A |
is this possible?
Thank you and appreciated for the help.
One row or one column?
What is your desired result?
Hello @Ritaf1983
sorry, I might not explain properly.
Right now, I make another table based on your formula and split your table using filter.
Here is how i split your table (for example: for Engine):
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |