Forum Discussion

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey,

 

thanks for providing sampledata, here you will find my solution a sample pbix file.

I extracted a sequence number from the column Visitname using the Extraxt Text Function "Text Between Delimiters", see the Applied Steps in the Query Editor:

 

This is necessary due to the fact that some kind of ordering (a sequence) is mandatory to determine a previous value.

 

Then i used a DAX statement to create a calculated column:

Previous SiteRateScore = 
var curSubjectNumber = 'score'[SubjectNumber]
var curSequence = 'score'[Sequence]
var prevSequence = 
CALCULATE(
    MAX('score'[Sequence])
    ,FILTER(ALL(score)
        ,'score'[SubjectNumber] = curSubjectNumber && 'score'[Sequence] < curSequence
    )
)
return
LOOKUPVALUE('score'[SiteRaterScore],score[SubjectNumber],curSubjectNumber,score[Sequence],prevSequence)

 

Here is a screenshot:

 

 

If this solution doesn't meet your requirement, please explain why, this will help me and others to provide another solution and also explain how a sequence within each SubjectNumber can be derived from the data you provided.

 

Regards

Tom

 

If this solves your question, please mark this post as answer, and also consider to give Kudo to this post, at least for honoring the time others spent trying to provide an answer.

 

 

 

chotu27's avatar
chotu27
Post Patron
8 years ago

TomMartens

 

Hi Yes that is what i wanted but im getting below error in calculation 

 

Previous SiteRateScore 1 = 
var curSubjectNumber = 'question 1'[SubjectNumber]
var curSequence = 'question 1'[Visit Sequence]
var prevSequence = 
CALCULATE(
    MAX('question 1'[Visit Sequence])
    ,FILTER(ALL('question 1')
        ,'question 1'[SubjectNumber] = curSubjectNumber && 'question 1'[Visit Sequence] < curSequence
    )
)
return
//prevSequence
LOOKUPVALUE('question 1'[SiteRaterScore],'question 1'[SubjectNumber],curSubjectNumber,'question 1'[Visit Sequence],prevSequence)
  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    The error indicates, that LOOKUPVALUE would retreive more than one value, this is not possible, so for this reason an Aggregate function has to been applied:

    Previous SiteRateScore 1 = 
var curSubjectNumber = 'question 1'[SubjectNumber]
var curSequence = 'question 1'[Visit Sequence]
var prevSequence = 
CALCULATE(
    MAX('question 1'[Visit Sequence])
    ,FILTER(ALL('question 1')
        ,'question 1'[SubjectNumber] = curSubjectNumber && 'question 1'[Visit Sequence] < curSequence
    )
)
return
//prevSequence
//LOOKUPVALUE('question 1'[SiteRaterScore],'question 1'[SubjectNumber],curSubjectNumber,'question 1'[Visit Sequence],prevSequence)
CALCULATE(
    AVERAGE('question 1'[SiteRaterScore])
    ,FILTER(ALL('question 1')
        ,'question 1'[SubjectNumber] = curSubjectNumber && 'question 1'[Visit Sequence] = prevSequence
    )
)
    • chotu27's avatar
      chotu27
      Post Patron
      8 years ago

      TomMartens Got it Worked but still it is not showing correct previous values please check attached  Pbix

       

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

        it's quite difficult to provide another solution. My example works with the data you provided, please explain what you expect, and show all the data.

         

        You have to be aware that data will be aggregated,, this is the reason why I choose the aggregate function AVERAGE, but maybe you have to choose SUM.

         

        Regards

        Tom