Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 table that shows me the total of each status as it currently sits so only the most recent status for each location.
I'm thinking I'll need to create a measure for each 'Status' that counts the number of times that status is the latest status per EnquiryId per Location.
This is an example of the data:
Location | EnquiryId | Status | Enquiry Date |
Location 1 | ID1 | Active | 11/15/2021 9:53:19 AM |
Location 1 | ID1 | In Progress | 11/17/2021 1:09:43 AM |
Location 1 | ID1 | Archived | 11/18/2021 1:09:43 AM |
Location 1 | ID2 | Active | 11/4/2021 10:59:50 PM |
Location 1 | ID2 | In Progress | 11/5/2021 1:24:29 AM |
Location 1 | ID2 | Active | 11/16/2021 10:10:20 PM |
Location 1 | ID2 | Enrolled | 11/17/2021 3:28:02 AM |
Location 1 | ID3 | Active | 11/3/2021 7:21:23 AM |
Location 1 | ID3 | Active | 11/3/2021 9:21:16 AM |
Location 2 | ID4 | Active | 11/15/2021 11:27:42 AM |
Location 2 | ID5 | Active | 10/8/2021 1:44:24 AM |
Location 2 | ID5 | Enrolled | 10/8/2021 1:44:58 AM |
Location 2 | ID6 | Active | 11/12/2021 8:50:15 AM |
Location 2 | ID6 | In Progress | 11/17/2021 10:38:32 PM |
Location 2 | ID6 | Enrolled | 11/22/2021 11:45:25 PM |
And this would be the resulting table with each column based on a measure:
Location | Current "Active" | Current "In Progress" | Current "Enrolled" | Current "Archived" |
Location 1 | 1 | 0 | 1 | 1 |
Location 2 | 1 | 0 | 2 | 0 |
Any help would be greatly appreciated.
Thanks.
Solved! Go to Solution.
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
Proud to be a Super User!
Hi @Kym_EVO
Try this measure:
Measure =
VAR _a =
SUMMARIZE (
SUMMARIZE (
ALL ( 'Table' ),
'Table'[Location],
'Table'[EnquiryId],
"MDate", MAX ( 'Table'[Enquiry Date] )
),
[MDate]
)
VAR _B =
CALCULATE (
COUNTA ( 'Table'[Status] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Location], 'Table'[Status], 'Table'[EnquiryId] ),
'Table'[Enquiry Date] IN _a
)
)
RETURN
_B
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Thanks very much @ryan_mayu, adding the extra column to show if it was the last date was what I was missing. Appreciate your help.
you are welcome
Proud to be a Super User!
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
Proud to be a Super User!