Summarizecolumn not Collect all Value in Origin Table

[Irwan]

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.

[Ritaf1983]

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

[Irwan]

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

[Ritaf1983]

Hi @Irwan 

please try this dax :

NewTable =

SUMMARIZE (

    'table',

    'table'[Component],

    'table'[Asset ID],

    "MaxSampledDate",

    CALCULATE ( MAX ( 'table'[Sampled Date] ) ),

    "Evaluation",

    CALCULATE (

        MAX ( 'table'[Evaluation] ),

        FILTER (

            'table',

            'table'[Sampled Date] = CALCULATE ( MAX ( 'table'[Sampled Date] ) )

        )

    )

)

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Irwan]

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: 

[Ritaf1983 · Accepted solution]

@Irwan 
You're right i corrected it :

NewTable =

SUMMARIZE (

    ADDCOLUMNS (

        SUMMARIZE (

            'table',

            'table'[Component],

            'table'[Asset ID]

        ),

        "MaxSampledDate",

        CALCULATE (

            MAX ( 'table'[Sampled Date] ),

            ALLEXCEPT ( 'table', 'table'[Component], 'table'[Asset ID] )

        )

    ),

    'table'[Component],

    'table'[Asset ID],

    [MaxSampledDate],

    "Evaluation",

    CALCULATE (

        MAX ( 'table'[Evaluation] ),

        FILTER (

            'table',

            'table'[Sampled Date] = [MaxSampledDate]

        )

   

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Irwan]

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.

[Ritaf1983]

Glad to help 🙂

[Irwan]

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.

[Ritaf1983]

Hi @Irwan 
sorry I don't understand what is column you need to split

[Irwan]

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.

[Ritaf1983]

One row or one column?
What is your desired result?

[Irwan]

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):

SUMMARIZECOLUMNS(

    'table'[Asset ID],

    "Engine Sampled Date",

    CALCULATE(MAX('table'[MaxSampledDate]),'table'[Component]="Engine"),

    "Engine Evaluation",

    CALCULATE(MAX('table'[Evaluation]),'table'[Component]="Engine"),

From the formula above, I tried to create a table like this.

So, I wonder if there is a way to split your table directly from original table (not making two table like I do right now).

Thank you and really appreciated for the help. Sorry for the confusing question.