topic Re: Summarize Columns bringing only the latest value per school in DAX Commands and Tips

Summarize Columns bringing only the latest value per school

maldonna — Tue, 23 Jul 2024 19:01:44 GMT

I was given an export of a form where users do not always fill in every field. I need to create a table from the export that groups by school and pulls the latest entry for each column ignoring blanks. I have tried multiple queries.

This is the query I have:

NewTable =

ADDCOLUMNS(

SUMMARIZE(

'Sample',

'Sample'[School]

"Colors", CALCULATE(FIRSTNONBLANK('Sample'[Colors], 'Sample'[Colors])),

"Food", CALCULATE(FIRSTNONBLANK('Sample'[Food], 'Sample'[Food])),

"Travel", CALCULATE(FIRSTNONBLANK('Sample'[travel], 'Sample'[travel]))

// ... add more columns as needed

)

The expectation is that I would get Ellis, green banana, japan and Porter, pink, pineap, italy. /

As you can see that is not the result. Any help would be appreciated.

Re: Summarize Columns bringing only the latest value per school

Anonymous — Wed, 24 Jul 2024 02:17:01 GMT

Hi, maldonna

I am glad to help you.

According to your description, you want to know how to summarize Columns bringing only the latest value per school?

If I understand you correctly, then you can refer to my solution.

Please consider using Index for sorting to get the latest value.

You can add a new column Index in Power Query Editor as shown below:

Then New Table, the specific formula is as follows:

NewTable = SUMMARIZE ( 'Sample', 'Sample'[School], "Colors", CALCULATE ( MAXX ( TOPN ( 1, FILTER ( 'Sample', 'Sample'[Colors] <> BLANK () ), 'Sample'[Index], DESC ), 'Sample'[Colors] ) ), "Food", CALCULATE ( MAXX ( TOPN ( 1, FILTER ( 'Sample', 'Sample'[Food] <> BLANK () ), 'Sample'[Index], DESC ), 'Sample'[Food] ) ), "Travel", CALCULATE ( MAXX ( TOPN ( 1, FILTER ( 'Sample', 'Sample'[Travel] <> BLANK () ), 'Sample'[Index], DESC ), 'Sample'[Travel] ) ) )

Finally you can realize the results you want:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Summarize Columns bringing only the latest value per school

Irwan — Wed, 24 Jul 2024 02:19:49 GMT

hello maldonna

please check if this accomodate your need.

Summarize =
SUMMARIZECOLUMNS(
    'Table'[School],
    "Last Color",
        var _LastIndexColor = CALCULATE(MAX('Table'[Index]),not 'Table'[Colors]=BLANK())
        Return
    CALCULATE(MAX('Table'[Colors]),'Table'[Index]=_LastIndexColor),
    "Last Food",
        var _LastIndexFood = CALCULATE(MAX('Table'[Index]),not 'Table'[Food]=BLANK())
        Return
    CALCULATE(MAX('Table'[Food]),'Table'[Index]=_LastIndexFood),
    "Last Travel",
        var _LastIndexTravel = CALCULATE(MAX('Table'[Index]),not 'Table'[Travel]=BLANK())
        Return
    CALCULATE(MAX('Table'[Travel]),'Table'[Index]=_LastIndexTravel)
)

As your requirement, you need to get the latest value so i created 'Index' column from Power Query for this example.

Otherwise, you need date/time to identify the latest value in your real tabledata.

Hope this will help you.

Thank you.