Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
106 | |
93 | |
69 |
User | Count |
---|---|
167 | |
132 | |
130 | |
96 | |
91 |