Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Replace blank with Not assigned

I'm having blank values  caused by relationship and with no matched record. But I want to replace it with "Not assigned" text
Need Help
Show and Tell
Tutorial Requests
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi Anonymous ,
    Thanks for using Microsoft Fabric Community Forum.
    Try using the below DAX to create a new column.

    Acct Assignment Order - Text (Updated) =
    VAR AcctAssignment =
        LOOKUPVALUE(
            'Table2'[Acct Assignment Order - Text],
            'Table2'[Order Key], 'Table1'[Order Key]
        )
    RETURN
    IF(
        ISBLANK(AcctAssignment) || AcctAssignment = "",
        "Not assigned",
        AcctAssignment
    )
    Here replace Table 2 with the table name in which there are blanks.
    I have replicated the scenario with sample data 

    Related two tables


    Used the newly created column in the visual

    Here you can see the blanks are being replaced by Not assigned.
    Hope this works for you.

    If this helps , please accept as solution to help others find easily and a kudos would be appreciated.

    Thank you.



v-veshwara-msft's avatar
v-veshwara-msft
Icon for Community Support rankCommunity Support
1 year ago

Hi Anonymous ,
Thanks for using Microsoft Fabric Community Forum.
Try using the below DAX to create a new column.

Acct Assignment Order - Text (Updated) =
VAR AcctAssignment =
    LOOKUPVALUE(
        'Table2'[Acct Assignment Order - Text],
        'Table2'[Order Key], 'Table1'[Order Key]
    )
RETURN
IF(
    ISBLANK(AcctAssignment) || AcctAssignment = "",
    "Not assigned",
    AcctAssignment
)
Here replace Table 2 with the table name in which there are blanks.
I have replicated the scenario with sample data 

Related two tables


Used the newly created column in the visual

Here you can see the blanks are being replaced by Not assigned.
Hope this works for you.

If this helps , please accept as solution to help others find easily and a kudos would be appreciated.

Thank you.



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    GREAT! It worked. But the DAX only worked for Import mode. Any idea how the DAX be working with Direct Query?

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Icon for Community Support rankCommunity Support
      1 year ago

      Glad that the DAX worked in Import mode.
      The reason it didnt work in DirectQuery is because DirectQuery limits complex DAX functions, leading to performance issues and potential query failures.
      LOOKUPVALUE can struggle in DirectQuery mode because it generates complex queries that the backend may not efficiently support.
      Use this DAX to create a new column for DirectQuery :

      Acct Assignment Order - Text (Updated) =
      IF(
          ISBLANK(RELATED('Table2'[Acct Assignment Order - Text])) || RELATED('Table2'[Acct Assignment Order - Text]) = "",
          "Not assigned",
          RELATED('Table2'[Acct Assignment Order - Text])
      )

      Hope this works.

      Thankyou.