Bizzare inconsistent column calculation that works on most rows but not on some

[masplin]

I Have a model that is looking up data from one table to another using this formula

CaseNoteCreatedInStatus Test = var _status = 
    CALCULATE (
        VALUES ( StatusLog[Status] ),
        
        FILTER (
            StatusLog,
            StatusLog[Adviser ID]=CaseNotes[FinalisedByAdviserID] &&
            StatusLog[StatusStartDate] = CaseNotes[CreatedDate]
                && CaseNotes[CreatedTime] >= StatusLog[StatusStartTime]
                && CaseNotes[CreatedTime] < StatusLog[StatusEndTime] 
                )
        )
RETURN IF( ISBLANK(_status), "Inactive",_status)

This doesnt rely on any relationships.  I just had to do a major rebuild and found there wewre some odd differences.   Mostly the formula is working fine, but occassionally for a specific user on a specifc day the formula returns blankwhne it shouldn't be.  it is fine for other users on same day andthat user on other days.

I made a small model of an example where the formula for the 7this working but 6th isnt.  Having exported the data and reinputt he formula it works fine.

Here is model Test Model 

I have tried installing latest version power BI, rebooting, but still the same.  i can understand if it never works, but not to work 99% of the time.  Has anyone come across this and got an explanation as makes me worry this could be happening elsewhere and I would never find it?

Thanks for any advice

Mike

[amitchandak]

@masplin , try count in place of values. Also do you need <= in case of StatusEndTime

CaseNoteCreatedInStatus Test = var _status = 
    CALCULATE (
        count ( StatusLog[Status] ),
        
        FILTER (
            StatusLog,
            StatusLog[Adviser ID]=CaseNotes[FinalisedByAdviserID] &&
            StatusLog[StatusStartDate] = CaseNotes[CreatedDate]
                && CaseNotes[CreatedTime] >= StatusLog[StatusStartTime]
                && CaseNotes[CreatedTime] < StatusLog[StatusEndTime] 
                )
        )
RETURN IF( ISBLANK(_status), "Inactive",_status)

[masplin]

My point is this same formula works in this column for this user for the 7/7 not 6/7. it works for other advisers on the 6th.  So nothing wrong with the formula. Also if you look in my test pbix the calculation works fine when the data is exported. It also works fine in my new version where i have done some changes to relationships that are not involved in this calculation.  So it is being erratic not wrong.

COUNT produces a blank. You cant put <= end time as the status log has a next row where starttime = previouys end time so you would get multiple rows pulled back.

Thnaks

Mike

[v-yiruan-msft]

Hi @masplin ,

Please update the formula as below:

CaseNoteCreatedInStatus Test =
VAR _status =
    CALCULATE (
        MAX ( StatusLog[Status] ),
        FILTER (
            StatusLog,
            StatusLog[Adviser ID] = CaseNotes[FinalisedByAdviserID]
                && StatusLog[StatusStartDate] = CaseNotes[CreatedDate]
                && CaseNotes[CreatedTime] >= StatusLog[StatusStartTime]
                && CaseNotes[CreatedTime] < StatusLog[StatusEndTime]
        )
    )
RETURN
    IF ( ISBLANK ( _status ), "Inactive", _status )

Best Regards

Rena

[masplin]

Hi StatusLog[Status] is text so wont max error?

Again my point is the formula is fine for most users on most days, but there are odd patches where it fails. It is also working fine on a rebuilt version of the model.  it's like the forumal engine is just failing on some specific adviserID/date combinations. 

[v-yiruan-msft]

Hi @masplin ,

You can check the below screen shot, it works well when used max function here even though StatusLog[Status] is text.

You use the VALUES function in your original formula, which returns a table with one or more columns with unique values. When the return values include multiple values, an error may occur when it used in the if condition...

Best Regards

Rena

[masplin]

Hi Rena

I checked and the formula actually returns a blank not an error so its find no vlaues not multiple values, but again only on some rows

[v-yiruan-msft]

Hi @masplin ,

Could you please provide the data which will return blank value by the calculated column "CaseNoteCreatedInStatus Test"? What's the function of this calculated column? It is used for getting the status of per Adviser ID in specific conditions,right?

Best Regards

Rena