URGENT Help - Filter previous state values of a record during calculations

[ApoorvaReddy]

Hi,

I have a scenario below where a course (object.name) has 2 records in the table. First record which was accessed (In-progress) by an user (actor.mbox) and later has completed it. I am trying to get a count of all courses that were still In-progress but never got completed by an user. Can someone assist me in getting the right expression to pass ?

“Inprogress Courses” = CALCULATE(DISTINCTCOUNT('Brand Excellence Academy_all'[object.name]),'Brand Excellence Academy_all'[verb.display]="accessed",'Brand Excellence Academy_all'[object.type]="course")

Requirement: Should also filter out courses if they are completed

“Completed Courses” = CALCULATE(DISTINCTCOUNT('Brand Excellence Academy_all'[object.name]),'Brand Excellence Academy_all'[verb.display]="completed",'Brand Excellence Academy_all'[object.type]="course")

Above expresssion works well for completed but “Inprogress Courses” should not take into count for those courses which are completed by an user.

I have used subtract function such as below but it calculates all completed and inprogress courses seperately and then subtracts at the end, it is not giving me the right count.

CALCULATE(DISTINCTCOUNT('Brand Excellence Academy_all'[object.name]),'Brand Excellence Academy_all'[verb.display]="accessed",'Brand Excellence Academy_all'[object.type]="course") - CALCULATE(DISTINCTCOUNT('Brand Excellence Academy_all'[object.name]),'Brand Excellence Academy_all'[verb.display]="completed",'Brand Excellence Academy_all'[object.type]="course")

Any help is really appreciated.

Thanks

[amitchandak · Accepted solution]

@ApoorvaReddy , Try a measure like

countx(filter(
summarize('Brand Excellence Academy_all','Brand Excellence Academy_all'[object.name], "_1",
CALCULATE(DISTINCTCOUNT('Brand Excellence Academy_all'[object.name]),'Brand Excellence Academy_all'[verb.display]="completed",'Brand Excellence Academy_all'[object.type]="course") , "_2",CALCULATE(DISTINCTCOUNT('Brand Excellence Academy_all'[object.name]),'Brand Excellence Academy_all'[verb.display]="accessed",'Brand Excellence Academy_all'[object.type]="course")),[_2]>0 && isblank([_1])),[object.name])

[ApoorvaReddy]

Hi @amitchandak -

First of all, thank you very much for the reply. I tried this and it works to an extent.

My requirement is if a Course A is accessed by User A and completed by User A, then it should be excluded in my count whereas if course A is accessed by User A and completed by User B, then it is still considered as accessed (inprogress) by User A and my count should include that record. I hope you got my point, to achieve it, I think we should also consider the user ( "actor.mbox") field as a filter in the expression. Would you be able to update the expression, thanks a lot.

[ApoorvaReddy]

I got this to work by passing actor.mbox as second group by column. 

Thanks much for your previous query.