Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |