Filtering history table to have only the last value

[Anonymous]

Hello

I have a history table similar to this:

id item state

1   1       1

2   1       2

3   2       1

4   3       1

5   4       1

6   4       2

7   4       3

but to make some filters I needd the table to be like this:

id item state

2   1       2

3   2       1

4   3       1

7   4       3

How can I do this? Is it possible

Thanks

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

If you want a report visual, you can drag the three columns into a table visual. And then change the summarization of [item] to Don't summarize, of [id] to Maximum, of [state] to Maximum.

If you want a new table, try this formula.

Table =
SUMMARIZE (
    'Table1',
    Table1[item],
    "id", MAX ( 'Table1'[id] ),
    "state", MAX ( 'Table1'[state] )
)

Best Regards!

Dale

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

If you want a report visual, you can drag the three columns into a table visual. And then change the summarization of [item] to Don't summarize, of [id] to Maximum, of [state] to Maximum.

If you want a new table, try this formula.

Table =
SUMMARIZE (
    'Table1',
    Table1[item],
    "id", MAX ( 'Table1'[id] ),
    "state", MAX ( 'Table1'[state] )
)

Best Regards!

Dale

[Anonymous]

Hello @v-jiascu-msft

Your solution almost solved my problem.

Now I do have the table filter as I want, but I need to use a power query function on that table. But due to the fact that the table is created I can not see it on the query editor.

How can I use a power query function in this case?

Thanks

[Anonymous · Accepted solution]

@v-jiascu-msftProblem solved. Using your solution as base, I reached a table combining power query.

Thanks

[Omega]

Do you want to filter the table at the query level or report level?

[Anonymous]

@Omegaat query level. in report level I think will not work due to relationships between tables.

[Omega]

Click on Edit Queries ->Choose the table from the left pane -> Filter your ID column

[Anonymous]

@OmegaI can not do it like that. The table is dynamic, I can not do that with all my ids. I need a "DAX way" or power query.