Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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.
Hi,
In the Query Editor, right click on the first coumn > Group By and selec Max.
Hi,
Share some data in a format that can be pasted in an MS Excel file and also show the expected result.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |