Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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):
User | Count |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |