Forum Discussion

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi chotu27,

     

    Maybe there is a solution you can give it a try. Please check it out in the attachment.

    1. In the Query Editor, sort the "SubjectNumber" first, then sort the column "VisitName". 

    2. Add an index to keep the order we make in step 1.

    3. Add a custom column.

    if [Index.1] = 0 then
    [Index.1] else if 
    [VisitName] = #"Added Index"{[Index.1] - 1}[VisitName] then 
    null else 
    [Index.1]

    4. Right-click the column "Custom", choose Fill -> Down.

    5. Apply the changes, and create a measure.

     

    Measure =
VAR lastIndex =
    CALCULATE (
        MAX ( 'Table'[Custom] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SubjectNumber] ),
            'Table'[Custom] < MAX ( 'Table'[Custom] )
        )
    )
RETURN
    IF (
        ISBLANK ( lastIndex ) = FALSE ()
            && HASONEVALUE ( 'Table'[VisitName] ),
        CALCULATE (
            SUM ( 'Table'[SiteRaterScore] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Custom] = lastIndex )
        ),
        0
    )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi chotu27,

     

    Is my solution helpful?

     

    Best Regards,

    Dale