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.
I have one ID column with nonunique IDs.
I have another column with dates.
I have a third column that has a 0 or 1 depending on a condition.
For each ID, I am trying to find the 0 or 1 value for the latest date in the date column grouped by ID and add that for each row for that ID in a new column. I'm trying to create the "Most Recent Active Status" in the below example:
ID | Dates | Active Status at Date | Most Recent Active Status |
1 | 11/1/2013 | 1 | 0 |
1 | 11/1/2015 | 0 | 0 |
1 | 5/1/2015 | 1 | 0 |
2 | 11/1/2012 | 1 | 1 |
2 | 11/1/2014 | 1 | 1 |
3 | 5/1/2014 | 0 | 1 |
3 | 11/1/2013 | 1 | 1 |
3 | 11/1/2015 | 1 | 1 |
3 | 5/1/2015 | 0 | 1 |
Solved! Go to Solution.
Hi @Anonymous ,
Please try to use the below measure, it works on my desktop:
Measure 8 = CALCULATE(MAX('Table (2)'[Most Recent Active Status]),FILTER(ALL('Table (2)'[Dates]),'Table (2)'[Dates] = CALCULATE(MAX('Table (2)'[Dates]),ALLEXCEPT('Table (2)','Table (2)'[ID]))))
Aiolos Zhao
Proud to be a Super User!
Try new columns
Max date = maxx(filter(table,table[ID] = earlier(table[ID]) ),table[Date])
Max Status = maxx(filter(table,table[ID] = earlier(table[ID]) && table[date] = earlier(table[Max date ])),table[status])
Hi @Anonymous ,
You can create a measure as below:
Measure =
var a =CALCULATE(MAX('Table'[Dates]),ALLEXCEPT('Table','Table'[ID]))
Return
CALCULATE(MAX('Table'[Active Status at Date]),'Table'[Dates]=a)
And you will see:
Or you can create a calculated column as below:
Column =
var a =CALCULATE(MAX('Table'[Dates]),ALLEXCEPT('Table','Table'[ID]))
var b= CALCULATE(MAX('Table'[Active Status at Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[Dates]=a)
Return
b
And you will see:
For the related .pbix file,pls click here.
Hi @Anonymous ,
You can create a measure as below:
Measure =
var a =CALCULATE(MAX('Table'[Dates]),ALLEXCEPT('Table','Table'[ID]))
Return
CALCULATE(MAX('Table'[Active Status at Date]),'Table'[Dates]=a)
And you will see:
Or you can create a calculated column as below:
Column =
var a =CALCULATE(MAX('Table'[Dates]),ALLEXCEPT('Table','Table'[ID]))
var b= CALCULATE(MAX('Table'[Active Status at Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[Dates]=a)
Return
b
And you will see:
For the related .pbix file,pls click here.
@v-kelly-msftThank you for you reply!
I used the calulated column approach since I'm not too familiar with measures. It worked beautifully. It was truly an elegant solution with those variables.
I'm still trying to get my head around the EARLIER() function.
Thanks for your help!
Try new columns
Max date = maxx(filter(table,table[ID] = earlier(table[ID]) ),table[Date])
Max Status = maxx(filter(table,table[ID] = earlier(table[ID]) && table[date] = earlier(table[Max date ])),table[status])
@amitchandakThank you for taking the time to respond. Your solution worked! I'm still trying to understand what EARLIER() does, but from playing with the code, it seems like it applies the result of the equation to all rows that meet the filter condition(s) rather than just the row where the maxx() is found.
Hi @Anonymous ,
Please try to use the below measure, it works on my desktop:
Measure 8 = CALCULATE(MAX('Table (2)'[Most Recent Active Status]),FILTER(ALL('Table (2)'[Dates]),'Table (2)'[Dates] = CALCULATE(MAX('Table (2)'[Dates]),ALLEXCEPT('Table (2)','Table (2)'[ID]))))
Aiolos Zhao
Proud to be a Super User!
@AiolosZhaoThank you for taking the time to respond to my question. I tried your solution, and it worked! I'm still trying to understand how measures differ from calculated columns. It looks like one cannot use measures in the Data view, but measures can be used when creating a table in the Report view. Is that right?
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |