Forum Discussion

cheezy's avatar
cheezy
Helper I
2 years ago
Solved

column number sequence reversal

see below - data comes in from our source system as in the first two columns - for example the most recent audit for ID = 1 is 4, ID =2 is 2 and ID = 3 is 6 what i require is the sequence reversed ,...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi cheezy -Can you please try the below code , i have checked with the above data shared 

    getting the required output as expected 

    Measure:

    Required =

    VAR MaxSequence = CALCULATE(MAX('YourTable'[Audit_Sequence]), ALLEXCEPT('YourTable', 'YourTable'[ID]))
    RETURN
    MaxSequence-MAX('YourTable'[Audit_Sequence]) +1
     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi cheezy -Create a calculated column to achieve the required sequence for the expected column

 

Required =
VAR MaxAudit = MAX(AuditData[MostRecentAudit])
RETURN
MaxAudit - AuditData[MostRecentAudit] + 1

 

replace the table name and columns as per your table/model references.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

cheezy's avatar
cheezy
Helper I
2 years ago

that doesnt quite work

VAR MaxAudit = MAX(AuditData[Audit_Sequence]) would return 6 as in my example above as applies to all ID's

How can i filter this based on ID? ie max should be 4 for ID 1 , 2 for ID 2 and 6 for ID3 ?