Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
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!
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
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!
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 ,
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
79 | |
63 | |
61 | |
59 |
User | Count |
---|---|
166 | |
114 | |
100 | |
73 | |
67 |