Forum Discussion
Solved
Conditional columns using DAX in Power BI
HI All,
I have a requirement to display a new column based on a two other columns from the same table.
Scenario:
For each contract no., look for the minimum 'contract received date' and then display the respective 'contract executed date' in the new column.
Sample data:
| Contract No. | Contract Received date | Contract Executed Date | New Column |
22 | 7/7/2022 | 4/17/2023 | 4/17/2023 |
22 | 7/7/2022 | 4/17/2023 | 4/17/2023 |
22 | 8/7/2022 | 1/11/2022 | 4/17/2023 |
22 | 8/7/2022 | 1/11/2022 | 4/17/2023 |
| 23 | 11/4/2023 | 7/8/2021 | 4/6/2021 |
| 23 | 11/4/2023 | 7/8/2021 | 4/6/2021 |
| 23 | 2/9/2023 | 4/6/2021 | 4/6/2021 |
| 23 | 2/9/2023 | 4/6/2021 | 4/6/2021 |
| 24 | 5/8/2021 | 1/1/2021 | 1/1/2021 |
| 24 | 5/8/2021 | 1/1/2021 | 1/1/2021 |
| 24 | 9/9/2023 | 2/2/2022 | 1/1/2021 |
| 24 | 9/9/2023 | 2/2/2022 | 1/1/2021 |
Could someone please help with the DAX to achieve this result?
Thank you
and you can try this
DateNew2 = VAR t1 = 'Table'[Contract No.] VAR t2 = ALLEXCEPT( 'Table', 'Table'[Contract No.] ) VAR t3 = CALCULATE( MIN( 'Table'[Contract Received date] ),t2 ) RETURN CALCULATE(MIN('Table'[Contract Executed Date]),t2, 'Table'[Contract No.] = t1 && 'Table'[Contract Received date] = t3 )
5 Replies
pls try this
DateNew = VAR t1 = 'Table'[Contract No.] VAR t2 = CALCULATE( MIN( 'Table'[Contract Received date] ), ALLEXCEPT( 'Table', 'Table'[Contract No.] ) ) RETURN MINX( FILTER( ALL( 'Table' ), 'Table'[Contract No.] = t1 && 'Table'[Contract Received date] = t2 ), 'Table'[Contract Executed Date] )and you can try this
DateNew2 = VAR t1 = 'Table'[Contract No.] VAR t2 = ALLEXCEPT( 'Table', 'Table'[Contract No.] ) VAR t3 = CALCULATE( MIN( 'Table'[Contract Received date] ),t2 ) RETURN CALCULATE(MIN('Table'[Contract Executed Date]),t2, 'Table'[Contract No.] = t1 && 'Table'[Contract Received date] = t3 )sorry you can shorten the code further try this
DateNew2 = VAR t1 = 'Table'[Contract No.] VAR t2 = ALLEXCEPT( 'Table', 'Table'[Contract No.] ) VAR t3 = CALCULATE( MIN( 'Table'[Contract Received date] ),t2 ) RETURN CALCULATE( MIN('Table'[Contract Executed Date]),t2, 'Table'[Contract Received date] = t3 )This worked too! thank you for the solution
