To find a min of 3 numbers in Dax in Direct query mode

[ShwetaPandey512]

Min(Min(D1,D2),D3) want to convert this into DAX that can be used in Direct Query mode

@PowerBI 

[bhanu_gautam · Accepted solution]

@ShwetaPandey512 , You are right it will not work in Direct query here is an old alternate solution if it helps

https://community.fabric.microsoft.com/t5/Desktop/Replacement-for-FIRSTNONBLANK-in-calculated-column...

[Anonymous · Accepted solution]

Hi bhanu_gautam ,thanks for the quick reply, I'll add more.

Hi @ShwetaPandey512 ,

Instead of creating calculated column, try creating measure with the following dax.

Measure = MIN(MIN(MAX([D1]),MAX([D2])),MAX([D3]))

Or use a nested if loop to create calculated columns, after my testing the min function seems to have limitations.

Column = 
VAR _d1 = [D1]
VAR _d2 = [D2]
VAR _d3 = [D3]
RETURN 
IF( _d1 < _d2,
  IF(_d1 < _d3,_d1,_d3),
  IF(_d2 < _d3,_d2,_d3)
)

Best Regards

[bhanu_gautam]

@ShwetaPandey512 , Try using

DAX
MINX(
{
(D1),
(D2),
(D3)
},
[Value]
)

In this DAX expression, a table is constructed with the values of D1, D2, and D3, and then MINX is used to find the minimum value from this table. This approach ensures that the calculation is compatible with Direct Query mode.

[ShwetaPandey512]

Thanks @bhanu_gautam  for the response but  getting below error
MINX is not allowed as part of calculated column DAX expression on Direct Query mode 

[bhanu_gautam · Accepted solution]

@ShwetaPandey512 , You are right it will not work in Direct query here is an old alternate solution if it helps

https://community.fabric.microsoft.com/t5/Desktop/Replacement-for-FIRSTNONBLANK-in-calculated-column...