The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi Everyone,
Trying to use MAX and IF functions(Based in excel) within a table to find a Date value (MAX_DATE) in a custom column. I want Max date based on AssetID to populate in the MaxDate column.
Here's an example of the dataset.
ASSET_ID | END_DATE | MAX_DATE |
100 | 6/30/2017 | 9/1/2019 |
100 | 6/30/2017 | 9/1/2019 |
100 | 9/1/2019 | 9/1/2019 |
100 | 6/30/2017 | 9/1/2019 |
101 | 6/30/2017 | 11/1/2019 |
101 | 11/1/2019 | 11/1/2019 |
101 | 6/30/2018 | 11/1/2019 |
102 | 6/30/2017 | 3/1/2020 |
102 | 3/1/2020 | 3/1/2020 |
102 | 6/30/2017 | 3/1/2020 |
102 | 9/1/2019 | 3/1/2020 |
103 | 12/31/2019 | 12/31/2021 |
103 | 12/31/2021 | 12/31/2021 |
Thanks in advance for your help!!!
Solved! Go to Solution.
If you can use a Measure instead, this should be simply:
MAX_DATE = MAX(Assets[END_DATE])
Then just put your ASSET_ID and this measure into a table visualization.
Another way to go would be to create an ASSET_IDS table with unique asset ids and a custom column in that table like so:
MAX_DATE = MAXX(RELATEDTABLE(Assets),[END_DATE])
Hi @danielgibbs,
In your scenario, please firstly open Query Editor and add an index column to your current table as shown in the following screenshot.
Secondly, create a new column using the formula below.
Max Date = CALCULATE(MAX(Table1[END_DATE]),FILTER(Table1,Table1[ASSET_ID]=EARLIEST(Table1[ASSET_ID])))
Thirdly, create a table visual using all the fields. For more details, you can check the example in the attached PBIX file.
Thanks,
Lydia Zhang
Hi @danielgibbs,
In your scenario, please firstly open Query Editor and add an index column to your current table as shown in the following screenshot.
Secondly, create a new column using the formula below.
Max Date = CALCULATE(MAX(Table1[END_DATE]),FILTER(Table1,Table1[ASSET_ID]=EARLIEST(Table1[ASSET_ID])))
Thirdly, create a table visual using all the fields. For more details, you can check the example in the attached PBIX file.
Thanks,
Lydia Zhang
thanks for your help on this!!
If you can use a Measure instead, this should be simply:
MAX_DATE = MAX(Assets[END_DATE])
Then just put your ASSET_ID and this measure into a table visualization.
Another way to go would be to create an ASSET_IDS table with unique asset ids and a custom column in that table like so:
MAX_DATE = MAXX(RELATEDTABLE(Assets),[END_DATE])
thanks for your help on this one!!
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |