Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
This is my data source named as "All Data":
ID | Form Completion time | Shop open/close date | Name of Branch | Status |
21 | 5/4/21 10:04:27 | 5/5/2021 | Gongabu | Open |
22 | 5/4/21 10:04:56 | 5/5/2021 | Maharajgunj | Close |
23 | 5/4/21 10:27:16 | 5/5/2021 | Swoyambhu | Open |
24 | 5/4/21 10:28:28 | 5/5/2021 | Thamel | Open |
25 | 5/4/21 11:35:56 | 5/5/2021 | Gongabu | Close |
26 | 5/4/21 10:36:24 | 5/5/2021 | Thamel | Close |
27 | 5/4/21 11:36:48 | 5/5/2021 | Maharajgunj | Open |
28 | 5/4/21 10:47:17 | 5/5/2021 | Chyamasingh | Open |
My Question:
For a certain "Shop open/close date", I want to display Name of a Branch and Status which is Based on a latest date in "Form completion time" column.
My desired output:
ID | Form Completion time | Shop open/close date | Name of Branch | Status |
23 | 5/4/21 10:27:16 | 5/5/2021 | Swoyambhu | Open |
25 | 5/4/21 11:35:56 | 5/5/2021 | Gongabu | Close |
26 | 5/4/21 10:36:24 | 5/5/2021 | Thamel | Close |
27 | 5/4/21 11:36:48 | 5/5/2021 | Maharajgunj | Open |
28 | 5/4/21 10:47:17 | 5/5/2021 | Chyamasingh | Open |
Steps I tried:
Trial 1: Measure = MAXX('All Data','All Data'[Form Completion time])
Trial 2: Measure = CALCULATE(COUNTA('All Data'[Status]),FILTER('All Data','All Data'[Form Completion time] = MAX('All Data'[Form Completion time])))
Trial 3: Measure = CALCULATE(COUNTA('All Data'[Status]),FILTER('All Data','All Data'[Form Completion time] = LASTDATE('All Data'[Form Completion time])))
But none of this works. Please suggest?
Solved! Go to Solution.
Hi @Sagun
I would have 2 options to solve your problem
1. You can create a new calculated column Islatest which indicates if the current row is based on the latest value of the name of the branch
IsLatest =
var Latestdate = CALCULATE(MAX('Table'[Form Completion time]),FILTER('Table','Table'[Name of Branch] = EARLIER('Table'[Name of Branch])))
RETURN
IF('Table'[Form Completion time] = Latestdate,1,0)
and then you can filter your table based on "IsLatest" value
2. You can create measure as Latest Status using the following formule
Latest Status =
var latestdate = CALCULATE(MAX('Table'[Form Completion time]),REMOVEFILTERS('Table'[Status]))
var lateststatus = LOOKUPVALUE('Table'[Status],'Table'[Form Completion time],latestdate)
RETURN
lateststatus
Proud to be a Super User!
@Sagun Hi sagun, in this case i created a summary table from the main table which i can use to show data in the matrix visual.
I created table which will extract the max date for each branch. then using lookup function we will get value from main table to summary table like below
I am also attaching pbix file for your reference.
Proud to be a Super User!
Thank you so much. It helped,:)
@Sagun Hi sagun, in this case i created a summary table from the main table which i can use to show data in the matrix visual.
I created table which will extract the max date for each branch. then using lookup function we will get value from main table to summary table like below
I am also attaching pbix file for your reference.
Proud to be a Super User!
Hi @Sagun
I would have 2 options to solve your problem
1. You can create a new calculated column Islatest which indicates if the current row is based on the latest value of the name of the branch
IsLatest =
var Latestdate = CALCULATE(MAX('Table'[Form Completion time]),FILTER('Table','Table'[Name of Branch] = EARLIER('Table'[Name of Branch])))
RETURN
IF('Table'[Form Completion time] = Latestdate,1,0)
and then you can filter your table based on "IsLatest" value
2. You can create measure as Latest Status using the following formule
Latest Status =
var latestdate = CALCULATE(MAX('Table'[Form Completion time]),REMOVEFILTERS('Table'[Status]))
var lateststatus = LOOKUPVALUE('Table'[Status],'Table'[Form Completion time],latestdate)
RETURN
lateststatus
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |