DAX Query

[gadlakha]

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.

[gadlakha]

Hey. Can someone kindly help with this?

[Anonymous]

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 Team

If 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

[Greg_Deckler]

@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
  __Result

[gadlakha]

Hi, 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:

[amitchandak]

@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

[gadlakha]

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.