Filter last date in query by entity

[gustavo_acqio]

Hello everyone, good afternoon and happy new year.

I need help with the query editor (i don't know a lot of power query), but i have a table that has a lot of versions of the same entity, with the update date of each entity. All i need is  to filter the latest version of each entity. Through dax i can do that and create another table filtering each entity by its last version, using the DAX formula:

ACTIVE_VERSION =
IF (
    AND (
        CALCULATE (
            COUNTA ( tenant[Document.id] );
            ALLEXCEPT ( tenant; tenant[Document.id] )
        ) = 2;
        CALCULATE (
            LASTDATE ( tenant[Document.UpdateTimeUtc] );
            ALLEXCEPT ( tenant; tenant[Document.id] )
        ) = tenant[Document.UpdateTimeUtc]
    );
    1;
    IF (
        CALCULATE (
            COUNTA ( tenant[Document.id] );
            ALLEXCEPT ( tenant; tenant[Document.id] )
        ) = 1;
        1;
        0
    )
)

By doing that, i need to follow up creating another table (calculated) where i copy this table filtering only the "ACTIVE_VERSION = 1" in this calculated column that i've created through DAX. The problem is: The table is getting a lot bigger, and having 2 of the same table is making it hard/slow for me to refresh my dashboard. Is there a way to do this through the Power Query? If so, can anybody teach me or give me the directions i need?

Thank you all

Gustavo Almeida

[v-lionel-msft]

Hi @gustavo_acqio ,

I created a measure and add the measure to the "Filters on this visual":

ACTIVE_VERSION = 
VAR x = 
CALCULATE(
    COUNT(tenant[Document.id]),
    ALLEXCEPT(
        tenant,
        tenant[Document.id]
    )
)
VAR y =
CALCULATE(
    LASTDATE(tenant[Document.UpdateTimeUtc]),
    ALLEXCEPT(
        tenant,
        tenant[Document.id]
    )
)
RETURN
IF(
    MAX(tenant[Document.UpdateTimeUtc]) = y && x = 2,
    1,
    IF(
        x = 1,
        1,0
    )
)

Is this what you want? 

If not, please give us a sample data model and describe in detail what you want to realize.
According to your description, I think you can realize the effect By DAX.

Best regards,
Lionel Chen

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

[gustavo_acqio]

Hi, i'm sorry it took me so long to reply, had an emergency.

My table is somewhat like that.

Costumer ID	Update date
1	01/01/2020
2	01/01/2020
3	01/01/2020
3	03/01/2020
4	01/01/2020
5	01/01/2020

Through dax, i achieved this:

Costumer ID	Update date	Last version
1	01/01/2020	1
2	01/01/2020	1
3	01/01/2020	0
3	03/01/2020	1
4	01/01/2020	1
5	01/01/2020	1

And then i created another table, based on that column "Last version", filtering only the costumers by their last update. The problem is, by doing so i have to duplicate my costumer table, having one with all the costumers versions and another one with only the last one. The thing is: I cant do any ETL outside of Power BI, i can't bring this to SQL to solve that problem, so i need to do it using M / Power Query, otherwise my Dashboard is going to reach the limit of 1 gb soon, thats why i need to fix it through Power Query.

Thank you all

Gustavo Almeida

[v-lionel-msft · Accepted solution]

Hi @gustavo_acqio ,

You can get a table like this in Edit Query:

1. Copy your original table.
2. right click [Costumer ID] column -> Group by -> Max 

3. Add "Costum column": column = 1

Best regards,
Lionel Chen

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

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the first coumn > Group By and selec Max.

[Ashish_Mathur]

Hi,

Share some data in a format that can be pasted in an MS Excel file and also show the expected result.

[amitchandak]

Try something like this

Measure = 
VAR __date = MAX ( tenant[Document.UpdateTimeUtc] )
RETURN CALCULATE ( Count ( tenant[Document.id ), VALUES ( tenant[Document.id ), tenant[Document.UpdateTimeUtc] = __date )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601