Forum Discussion
How to get the latest version data in matrix
Data is available in Version row wise
we need to take latest version (the latest version in above screenshot is 7) so it need to display 1080 (PROD_QTY) as bellow screenshot .
but i am trying in power bi it was showing the data with sum of 1080+1080 = 2160 as below screenshot.
Requirement is need to get the latest version data according to Row_insert_TSTMP , MTOC , Plan_Period_day columns .
Hi Anonymous
You can get only the last version in a few ways.
1. You can load to the model only the last version with these few steps in the power query.
Let's imagine we have a simple table with 2 columns:In the first step we can find the max value of the version :
Note that the previous step should be a part of the formula (Changed type)
=List.Max(#"Changed Type"[#"Version "])As we have this formula we can compare it to the version with the conditional column :
In the next step just filter the table by this comparing column
And delete unnecessary columns
(these steps will be repeated every report refresh )
So you will load only last version rows.Second option
If the goal is to create a filter for the visual only, you can create a measure with a similar logic :
Measure testing the version :
Test last version = CALCULATE(max('Table 2'[Version ]),all('Table 2'))And filtered measure for the matrix :
Fitered sum =CALCULATE(sum('Table 2'[Value]),FILTER('Table 2','Table 2'[Version ]=[Test last version]))Result:
The pbix with both versions of the solution is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
3 Replies
Hi Anonymous
You can get only the last version in a few ways.
1. You can load to the model only the last version with these few steps in the power query.
Let's imagine we have a simple table with 2 columns:In the first step we can find the max value of the version :
Note that the previous step should be a part of the formula (Changed type)
=List.Max(#"Changed Type"[#"Version "])As we have this formula we can compare it to the version with the conditional column :
In the next step just filter the table by this comparing column
And delete unnecessary columns
(these steps will be repeated every report refresh )
So you will load only last version rows.Second option
If the goal is to create a filter for the visual only, you can create a measure with a similar logic :
Measure testing the version :
Test last version = CALCULATE(max('Table 2'[Version ]),all('Table 2'))And filtered measure for the matrix :
Fitered sum =CALCULATE(sum('Table 2'[Value]),FILTER('Table 2','Table 2'[Version ]=[Test last version]))Result:
The pbix with both versions of the solution is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Happy to help 🙂