Forum Discussion

Kym_EVO's avatar
Kym_EVO
Frequent Visitor
4 years ago
Solved

Count only the latest values for filtered string

Hello,   We have multiple locations and receive enquiries from multiple people each with a unique ID. When their enquiry status is updated it is timestamped and I'm wanting to create a snapshot tab...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    Kym_EVO 

    you can create a column

    IfLast = 
VAR _last=CALCULATE(max('Table'[Enquiry Date]),ALLEXCEPT('Table','Table'[Location],'Table'[EnquiryId]))
return if('Table'[Enquiry Date]=_last,"Y","N")

    then create four measures

    active = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Active")+0

inprogress = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="In Progress")+0

enrolled = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Enrolled")+0

archived = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Archived")+0

    pls see the attachment below

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
4 years ago

Kym_EVO 

you can create a column

IfLast = 
VAR _last=CALCULATE(max('Table'[Enquiry Date]),ALLEXCEPT('Table','Table'[Location],'Table'[EnquiryId]))
return if('Table'[Enquiry Date]=_last,"Y","N")

then create four measures

active = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Active")+0

inprogress = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="In Progress")+0

enrolled = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Enrolled")+0

archived = CALCULATE(COUNTROWS('Table'),'Table'[IfLast]="Y",'Table'[Status]="Archived")+0

pls see the attachment below