Forum Discussion
DAX Query
Hi All,
I have a table that lists the Release_IDs, Release_Versions, Release_Category, Release_BaseVersion and a flag to specify if a given release was external or not.
| ID | Version | Category | Base | isRelease |
| 616 | XYZ.1.7.1 | master | 1.7 | False |
| 616 | XYZ.1.7.1 | master | 1.7 | False |
| 648 | XYZ.1.7.5 | master | 1.7 | False |
| 654 | XYZ.1.7.6 | master | 1.7 | True |
| 655 | XYZ.1.7.7 | master | 1.7 | False |
| 763 | XYZ.1.7.34 | master | 1.7 | False |
| 1423 | XYZ.1.9.0.2 | master | 1.9.0 | False |
| 1427 | XYZ.1.9.0.3 | master | 1.9.0 | True |
| 1460 | XYZ.1.9.0.12 | master | 1.9.0 | False |
| 1463 | XYZ.1.9.0.13 | master | 1.9.0 | False |
| 876 | XYZ.1.7.59 | master | 1.7 | False |
| 1003 | XYZ.1.8.0.9 | master | 1.8.0 | False |
| 1004 | XYZ.1.7.0.5 | r1.7.0 | 1.7.0 | False |
| 1005 | XYZ.1.7.0.5 | r1.7.0 | 1.7.0 | True |
| 1007 | XYZ.1.7.82 | master | 1.7.0 | False |
| 1169 | XYZ.1.8.0.32 | master | 1.8.0 | False |
| 1466 | XYZ.1.9.0.12 | master | 1.9.0 | True |
| 1468 | XYZ.1.9.0.14 | master | 1.9.0 | False |
| 1593 | XYZ.1.9.0.45 | master | 1.9.0 | False |
| 1598 | XYZ.1.9.0.46 | master | 1.9.0 | True |
| 1603 | XYZ.1.9.0.47 | master | 1.9.0 | False |
| 1171 | XYZ.1.7.0.32 | r1.7.0 | 1.7.0 | False |
| 1162 | XYZ.1.8.0.29 | master | 1.8.0 | True |
What I want is to add a new column that would state the last release build version based on Release_Category and Release_BaseVersion. A given Release_Category and Release_BaseVersion could have multiple external releases and thus all subsequent Release_ID should point to the last available release build version for a given category.
Any help would be greatly appreciated.
Regards.
6 Replies
gadlakha , not very clear.
a new column
= if(maxx(filter(Table, [Category] = earlier([category]) && [version] = earlier([version]) ), [ID]) =[ID] 1,0)
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
Hey, Thanks for your reply.
I did tried the solution but the result is not as expected. Below is what I get as a new column with your proposed solution:
I am confused what a 1 or 0 denotes here.
The expected outcome would be as below:
Appreciate if you could help.
gadlakha Try:
Category = VAR __Category = [Category] VAR __Base = [Base] VAR __Max = MAXX(FILTER(ALL('Table'),[Category] = __Category && [Base] = __Base),[ID]) VAR __Result = MAXX(FILTER(ALL('Table'),[ID] = __ID),[Version]) RETURN __ResultHi, Thanks for the reply. I did tried it out but the data is not as expected. Below is what I get when I add a new column based on your reply:
As you could see, this is not right. For the very first row, the last available release for category: master and base: 1.7 is with ID: 654. Thus, any row with category: master, base: 1.7 and ID less than 654 should not have an entry.
The right expected data set would be as below:
Hey. Can someone kindly help with this?
Hi gadlakha ,
Plese try this:
LastRelId = CALCULATE ( MAX ( 'Table'[ID] ), FILTER ( ALL ( 'Table' ), 'Table'[isRelease] = TRUE () && 'Table'[Category] = EARLIER ( 'Table'[Category] ) && 'Table'[Base] = EARLIER ( 'Table'[Base] ) && 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ) )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
