Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Workaround for IFERROR in DAX using DirectQuery

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
)
)
)

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        amitchandak
        Super User
        5 years ago

        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
        )
        )
        )

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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.