Filter table

[Syndicate_Admin]

I have this table:

I need to get at the month level, for each DocumentTypeId, the row corresponding to its highest UpdateTime and its maximum possible StateId.

For example for the month of January:

Thank you.

[v-yueyunzh-msft · Accepted solution]

Hi, @Syndicate_Admin 

According to your description, you want to filter the table by grouping by month according to [documentid]. Right?

Here are the steps you can follow：

(1)This is my test date:

(2)We can click “New Table” at the top of the interfaceand enter :

Table =
SELECTCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE (
            'Test',
            'Test'[month],
            'Test'[DocumentTypeId],
            "UpdateTime", MAX ( 'Test'[UpdateTime] ),
            "Id", CALCULATE ( MAX ( 'Test'[Id] ) )
        ),
        "StateId",
            CALCULATE (
                MAX ( 'Test'[StatedId] ),
                'Test'[DocumentTypeId] = EARLIER ( [DocumentTypeId] )
                    && 'Test'[UpdateTime] = EARLIER ( [UpdateTime] )
            )
    ),
    "Id", [Id],
    "UpdateTime", [UpdateTime],
    "StateId", [StateId],
    "DocumentTypeId", [DocumentTypeId]
)

(3)Then we can get the table you need:

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

This is my test .pbix file : Filter_Dax.pbix 

Best Regards,

Aniya Zhang

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

[amitchandak]

@Syndicate_Admin , refer if my blog on a similar topic can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...