Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Irwan
Memorable Member
Memorable Member

Summarizecolumn not Collect all Value in Origin Table

Hello Power BI Expert,

 

need an advice for summarizecolumn dax.

Here is the origin table :

Irwan_2-1696201647877.png

and here is the result :

Irwan_3-1696201821978.png

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.

1 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]
        )
   Ritaf1983_0-1696209688386.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

12 REPLIES 12
Ritaf1983
Super User
Super User

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

hello @Ritaf1983 

here is the origin data

Sampled DateAsset IDComponentEvaluation
09-Feb-23DELLO_CO04ENGINEA
05-Nov-22DELLO_CO05ENGINEA
30-Sep-22DELLO_CO05ENGINEA
22-Jul-22DELLO_CO05ENGINEA
13-May-22DELLO_CO05ENGINEA
22-Apr-22DELLO_CO05ENGINEA
18-Mar-22DELLO_CO05ENGINEA
24-Feb-22DELLO_CO04ENGINEA
04-Feb-22DELLO_CO04ENGINEA
28-Jan-22DELLO_CO05ENGINEA
17-Feb-23DELLO_CO05ENGINEC
17-Nov-22DELLO_CO04ENGINEC
18-Apr-22DELLO_CO04ENGINEC
29-Oct-21DELLO_CO05ENGINEC
28-Oct-21DELLO_CO04ENGINEC
23-Jun-23DELLO_CO04ENGINEX
10-Aug-23DELLO_CO04TRANSMISSIONC
03-Aug-23DELLO_CO04TRANSMISSIONC
17-Nov-22DELLO_CO04TRANSMISSIONC
01-Sep-23DELLO_CO05TRANSMISSIONA
17-Feb-23DELLO_CO05TRANSMISSIONA
05-Nov-22DELLO_CO05TRANSMISSIONA
22-Jul-22DELLO_CO05TRANSMISSIONA
24-Jun-22DELLO_CO05TRANSMISSIONA
16-Jun-22DELLO_CO04TRANSMISSIONA
13-May-22DELLO_CO05TRANSMISSIONA
22-Apr-22DELLO_CO05TRANSMISSIONA
18-Mar-22DELLO_CO05TRANSMISSIONA
24-Feb-22DELLO_CO04TRANSMISSIONA
28-Jan-22DELLO_CO05TRANSMISSIONA
28-Oct-21DELLO_CO04TRANSMISSIONA
23-Jun-23DELLO_CO04TRANSMISSIONB
09-Feb-23DELLO_CO04TRANSMISSIONB
29-Dec-22DELLO_CO04TRANSMISSIONB
30-Sep-22DELLO_CO05TRANSMISSIONB
12-May-22DELLO_CO04TRANSMISSIONB
18-Apr-22DELLO_CO04TRANSMISSIONB
04-Feb-22DELLO_CO04TRANSMISSIONB
10-Dec-21DELLO_CO05TRANSMISSIONB
25-Nov-21DELLO_CO04TRANSMISSIONB
29-Oct-21DELLO_CO05TRANSMISSIONB
01-Sep-23DELLO_CO05ENGINEB
03-Aug-23DELLO_CO04ENGINEB
29-Dec-22DELLO_CO04ENGINEB
24-Jun-22DELLO_CO05ENGINEB
16-Jun-22DELLO_CO04ENGINEB
12-May-22DELLO_CO04ENGINEB
10-Dec-21DELLO_CO05ENGINEB
25-Nov-21DELLO_CO04ENGINEB

 

Expected result in New Table : 

Sampled DateAsset IDComponentEvaluation
1-Sep-23DELLO_CO05TRANSMISSIONA
1-Sep-23DELLO_CO05ENGINEB
10-Aug-23DELLO_CO04TRANSMISSIONC
3-Aug-23DELLO_CO04ENGINEB

 

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 :

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] ) )
        )
    )
)
Ritaf1983_0-1696207456461.png

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.

 
Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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:

Irwan_0-1696208920552.png

Value on original table: 

Irwan_1-1696208995867.png

 

@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]
        )
   Ritaf1983_0-1696209688386.png

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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 🙂

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Irwan
Memorable Member
Memorable Member

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 IDSampled Date1Component1Evaluation1Sampled Date2Component2Evaluation2
DELLO_CO0403/08/2023ENGINEB10/08/2023TRANSMISSIONC
DELLO_CO0501/09/2023ENGINEB01/09/2023TRANSMISSIONA

 

Thank you and really appreciated for the help.

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hello @Ritaf1983 

the table will be looking like this. The information of same asset id will be in one row.

Asset IDSampled Date1Component1Evaluation1Sampled Date2Component2Evaluation2
DELLO_CO0403/08/2023ENGINEB10/08/2023TRANSMISSIONC
DELLO_CO0501/09/2023ENGINEB01/09/2023TRANSMISSIONA

is this possible?

Thank you and appreciated for the help.

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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.
Irwan_0-1696214958182.png
 
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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.