Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |