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 , so column  C "Required"  with the most recent audit always = 1

any ideas on how to achieve this ? thanks

 

 

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!!

5 Replies

  • 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 ?

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        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!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Your solutions is great rajendraongole1 , and it worked!

    Hi, cheezy 

    Has the solution provided by rajendraongole1 solved your current problem? I've found that the solution provided by rajendraongole1 works well as a measure. 

     

    Best Regards

    Jianpeng Li