Filter last date in query by entity

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

Need Help

v-lionel-msft
6 years ago
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.

6 Replies

amitchandak
Super User
6 years ago
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Ashish_Mathur
Super User
6 years ago
Hi,

Share some data in a format that can be pasted in an MS Excel file and also show the expected result.
v-lionel-msft
Community Support
6 years ago
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
  Frequent Visitor
  6 years ago
  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
  - Ashish_Mathur
    Super User
    6 years ago
    Hi,
    
    In the Query Editor, right click on the first coumn > Group By and selec Max.