Workaround for IFERROR in DAX using DirectQuery

[Anonymous]

I'm currently migrating a PBI report from Import mode to DirectQuery. But apparently this mode doesn't have IFERROR. Is there another function/workaround for this issue? The column is defined as follows:

value =

VAR z = MIN(

MAX(

IFERROR(

RELATED('Table'[x]),

BLANK()),

0.1),

10000000)*[y]

RETURN IF(z>=2000,

ROUND(z,-2),

IF(z>1,

ROUND(z,0),

IF(z=0,

0,

1

)

)

)

[daxer-almighty]

Yes, RELATED works in DQ as one can check in dax.guide. But why would you expect RELATED to throw an error? That is what bothers me a lot since this means you're not sure if there's going to be a relationship between the tables. By the by, one can easily remove RELATED and use LOOKUPVALUE instead.

[amitchandak]

@Anonymous , I doubt even related will work in the direct query in a column , You may have convert this into a measure

[Anonymous]

@amitchandak I tried to convert it into a measure. Now the error is the following:
The column 'Table[x]' either doesn't exist or doesn't have a relationship to any table available in the current context.

The weird thing is that the column exists. Not only that but I also have a relationship between the current table and the main 'Table', with cardinality *:1, with cross filter direction on "Both" and the relationship is active.

[amitchandak]

@Anonymous , Try like

value =
VAR z = MINX('Table2',
MAXX('Table2',
IFERROR(
RELATED('Table'[x]),
BLANK()),
0.1),
10000000)*[y]
RETURN IF(z>=2000,
ROUND(z,-2),
IF(z>1,
ROUND(z,0),
IF(z=0,
0,
1
)
)
)

[Anonymous]

I tried to remove the numbers on the MAXX and MINX, now both functions have 2 arguments. But the error is:

OLE DB or ODBC error: Exception from HRESULT: 0x80040E57

[Anonymous]

@amitchandak Tried it, but there's another error:

Too many arguments were passed to the MAXX function. The maximum argument count for the function is 2.

The thing is, I need to find the highest (and lowest with MIN function) between two expression, not only in a certain column.