topic Re: URGENT - Calculate count of records filtering out previous state records in DAX Commands and Tips

URGENT - Calculate count of records filtering out previous state records

ApoorvaReddy — Tue, 30 Jun 2020 15:33:24 GMT

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.

Any help is really appreciated.

Thanks

Re: URGENT - Calculate count of records filtering out previous state records

Pulkit — Tue, 30 Jun 2020 15:50:03 GMT

You can subtract the two. For eg: Inprogress - Completed so if any course gets completed then it will get subtracted from in progress.

Re: URGENT - Calculate count of records filtering out previous state records

ApoorvaReddy — Tue, 30 Jun 2020 16:00:25 GMT

I am sorry, but i am new to DAX, can you use my expression above for "In-Progress" to modify and send it here.

Really appreciate your assistance !

Re: URGENT - Calculate count of records filtering out previous state records

ApoorvaReddy — Tue, 30 Jun 2020 16:13:48 GMT

I think i got it to work, used below expression and it worked.

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")