Forum Discussion
Post PatronDetermine MIN date across tables using DAX
I come from the PowerApps world, just getting familiar with DAX and PowerQuery. In PowerApps, to determine the minimum value of a set, I'd write something like:
MIN(value1, value2, value3, etc.)
I'm unable to make this work with DAX.
Scenario:
- Timestamp column in 4 different tables
- I need the BEGIN_DATE of my DATE_TABLE to be the earliest timestamp shown in any table
- And END_DATE of DATE_TABLE to be the latest timestamp shown in any table (I'll use MAX for this once I figure out MIN!)
Tried:
MIN(table1[ts1], table2[ts2], table3[ts3], etc.).
But DAX doesn't like the second parameter (nor any parameter thereafter)
What am I missing?
Thank you
Try something like this if you want the min value of 4 columns:
Measure = var m= IF(MIN('Table1'[ts1])<=MIN('Table2'[ts2]),MIN('Table1'[ts1]), IF(MIN('Table2'[ts2])<=MIN('Table3'[ts3]),MIN('Table2'[ts2]),MIN('Table3'[ts3]))) Return IF(m<=MIN('Table4'[ts4]),m,MIN('Table4'[ts4]))Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quicklyHi Anonymous ,
I was able to translate your solution to my use case. Since I am generating a Date Table based on timestamps in 3 tables, rather than a Measure, here is what I used: (Thank you!)
DATE_TABLE = VAR MIN_TS = IF( MIN(TABLE1[TIMESTAMP]) <= MIN(TABLE2[TIMESTAMP]), MIN(TABLE1[TIMESTAMP]), IF( MIN(TABLE2[TIMESTAMP]) <= MIN(TABLE3[TIMESTAMP]), MIN(TABLE2[TIMESTAMP]), MIN(TABLE3[TIMESTAMP]) ) ) VAR MAX_TS = IF( MAX(TABLE1[TIMESTAMP]) >= MAX(TABLE2[TIMESTAMP]), MAX(TABLE1[TIMESTAMP]), IF( MAX(TABLE2[TIMESTAMP]) >= MAX(TABLE3[TIMESTAMP]), MAX(TABLE2[TIMESTAMP]), MAX(TABLE3[TIMESTAMP]) ) ) RETURN CALENDAR( MIN_TS, MAX_TS )Sure would be more intuitive if DAX allowed something like:
DATE_TABLE = CALENDAR( MIN(TABLE1[TIMESTAMP], TABLE2[TIMESTAMP], TABLE3[TIMESTAMP]), MAX(TABLE1[TIMESTAMP], TABLE2[TIMESTAMP], TABLE3[TIMESTAMP]) )Oh well! Job security!
5 Replies
Try something like this if you want the min value of 4 columns:
Measure = var m= IF(MIN('Table1'[ts1])<=MIN('Table2'[ts2]),MIN('Table1'[ts1]), IF(MIN('Table2'[ts2])<=MIN('Table3'[ts3]),MIN('Table2'[ts2]),MIN('Table3'[ts3]))) Return IF(m<=MIN('Table4'[ts4]),m,MIN('Table4'[ts4]))Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
ericOnlineHi Anonymous ,
I was able to translate your solution to my use case. Since I am generating a Date Table based on timestamps in 3 tables, rather than a Measure, here is what I used: (Thank you!)
DATE_TABLE = VAR MIN_TS = IF( MIN(TABLE1[TIMESTAMP]) <= MIN(TABLE2[TIMESTAMP]), MIN(TABLE1[TIMESTAMP]), IF( MIN(TABLE2[TIMESTAMP]) <= MIN(TABLE3[TIMESTAMP]), MIN(TABLE2[TIMESTAMP]), MIN(TABLE3[TIMESTAMP]) ) ) VAR MAX_TS = IF( MAX(TABLE1[TIMESTAMP]) >= MAX(TABLE2[TIMESTAMP]), MAX(TABLE1[TIMESTAMP]), IF( MAX(TABLE2[TIMESTAMP]) >= MAX(TABLE3[TIMESTAMP]), MAX(TABLE2[TIMESTAMP]), MAX(TABLE3[TIMESTAMP]) ) ) RETURN CALENDAR( MIN_TS, MAX_TS )Sure would be more intuitive if DAX allowed something like:
DATE_TABLE = CALENDAR( MIN(TABLE1[TIMESTAMP], TABLE2[TIMESTAMP], TABLE3[TIMESTAMP]), MAX(TABLE1[TIMESTAMP], TABLE2[TIMESTAMP], TABLE3[TIMESTAMP]) )Oh well! Job security!
CALENDARAUTO() would also give you the result by scanning all tables in your data set with dates and providing the correct range, you can also change the starting month number - for example July with CALENDARAUTO(6)
- az38
Community Champion
- ericOnline
az38 ,
It appears that MIN() only accepts two values. Adding all four table columns in a single MIN() statement results in the error: `Unexpected Parameter` columns 3 and 4.
