Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, folks,
First of all, this forum is amazing.
I want to create the "Max Date by Value" column, which includes the largest date for each ID group that has the value of 2.
Here is what I want such a table to look like.
ID | Date | Value | Max Date by Value |
1 | 11/1/2015 | 4 | 5/1/2013 |
1 | 5/1/2014 | 5 | 5/1/2013 |
1 | 5/1/2013 | 2 | 5/1/2013 |
2 | 11/1/2012 | 1 | Null |
3 | 1/1/2013 | 2 | 1/1/2016 |
3 | 6/1/2010 | 5 | 1/1/2016 |
3 | 11/1/2016 | 4 | 1/1/2016 |
3 | 1/1/2016 | 2 | 1/1/2016 |
3 | 7/1/2017 | 7 | 1/1/2016 |
Here is the code I am playing with that is not working:
Solved! Go to Solution.
Try
Column
Max Date by Value = if([Value]=2, blank(),maxx(filter('Table', [ID]=earlier([ID])),[Date]))
Measure
Max Date by Value = calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID]),[Value]<>2)
Max Date by Value = if(max([Value]=2),blank(),calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID])))
Try
Column
Max Date by Value = if([Value]=2, blank(),maxx(filter('Table', [ID]=earlier([ID])),[Date]))
Measure
Max Date by Value = calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID]),[Value]<>2)
Max Date by Value = if(max([Value]=2),blank(),calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID])))
I think I just figured it out.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |