Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone,
I hope someone can help me with this. I have 2 tables, I need to get the latest value from the second table based on datetime
Table 1:
ID | Region |
1 | AMS |
2 | APJ |
3 | EMEA |
4 | AMS |
5 | APJ |
Table 2 :
ID | Maturity | Latest Update |
1 | sample1.1 | 2/1/2021 15:45 |
2 | sample2.1 | 2/1/2021 4:00 |
3 | sample3.1 | 2/1/2021 0:00 |
4 | sample4.1 | 2/1/2021 0:00 |
5 | sample5.1 | 2/1/2021 0:00 |
1 | sample1.2 | 2/3/2021 0:00 |
1 | sample1.3 | 2/10/2021 4:00 |
2 | sample2.2 | 2/10/2021 5:00 |
3 | sample3.2 | 2/5/2021 0:00 |
3 | sample3.3 | 2/11/2021 18:00 |
result :
ID | Region | Maturity | Latest Update |
1 | AMS | sample1.3 | 2/10/2021 4:00 |
2 | APJ | sample2.2 | 2/10/2021 5:00 |
3 | EMEA | sample3.3 | 2/11/2021 18:00 |
4 | AMS | sample4.1 | 2/1/2021 0:00 |
5 | APJ | sample5.1 | 2/1/2021 0:00 |
Thank you!
Solved! Go to Solution.
@Anonymous , New column in table 1
date = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
new column =
var _max = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
return
maxx(filter(Table2, Table1[ID] = Table2[ID] && Table2[Latest Update] = _max ),Table[Maturity] )
@Anonymous
Here is one way. First the model:
Now create the measure:
Latest Update (measure) =
VAR MXLU =
MAXX (
FILTER ( ALL ( Table2 ), Table2[ID] = SELECTEDVALUE ( 'dim Region'[ID] ) ),
Table2[Latest Update]
)
RETURN
IF ( MAX ( Table2[Latest Update] ) = MXLU, MAX ( Table2[Latest Update] ) )
Set up a table visual (using the region fields from the dim table):
Proud to be a Super User!
Paul on Linkedin.
Hi @Anonymous ,
Create a measure as below:
_Maturity =
CALCULATE(MAX('Table 2'[Maturity]),FILTER('Table 2','Table 2'[Latest Update]=[_last update]&&'Table 2'[ID]=MAX('Table 1'[ID])))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Anonymous
Here is one way. First the model:
Now create the measure:
Latest Update (measure) =
VAR MXLU =
MAXX (
FILTER ( ALL ( Table2 ), Table2[ID] = SELECTEDVALUE ( 'dim Region'[ID] ) ),
Table2[Latest Update]
)
RETURN
IF ( MAX ( Table2[Latest Update] ) = MXLU, MAX ( Table2[Latest Update] ) )
Set up a table visual (using the region fields from the dim table):
Proud to be a Super User!
Paul on Linkedin.
Hi Paul,
Thanks for this. The column for Maturity is sorted as Last Value. Can the measure that you provided be used in the Maturity as well if I have random values (cannot be alpha-sorted)?
@Anonymous
Sorry, I don't understand what you mean. Care to provide an example/depiction?
Proud to be a Super User!
Paul on Linkedin.
For the column of Maturity, I was able to get the last value since it was sorted as "Last". I was thinking if there is a measure to get the latest value of Maturity based on date as some of the values will not be aphabetical.
really appreciate your wor here!
Hi @Anonymous ,
Create a measure as below:
_Maturity =
CALCULATE(MAX('Table 2'[Maturity]),FILTER('Table 2','Table 2'[Latest Update]=[_last update]&&'Table 2'[ID]=MAX('Table 1'[ID])))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
I posted a measure which will return the rows with the latest date by region. So yes, it will return the latest "Maturity". Set up a table visual as I explained above
Proud to be a Super User!
Paul on Linkedin.
@Anonymous , New column in table 1
date = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
new column =
var _max = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
return
maxx(filter(Table2, Table1[ID] = Table2[ID] && Table2[Latest Update] = _max ),Table[Maturity] )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |