Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ApoorvaReddy
Frequent Visitor

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

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 ?

 

ApoorvaReddy_1-1593537723390.png

 

 

“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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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])

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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])

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.

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

 

Thanks much for your previous query.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors