Determine MIN date across tables using DAX

[ericOnline]

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

[V-pazhen-msft · Accepted solution]

@ericOnline 

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

[V-pazhen-msft · Accepted solution]

@ericOnline 

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

[ericOnline · Accepted solution]

Hi @V-pazhen-msft , 

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!

[admb448]

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]

Hi @ericOnline 

try this technique

MIN(table1[ts1], MIN(table2[ts2], MIN(table2[ts3], table3[ts4])))

[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.