Replace blank with Not assigned

[marbi]

I'm having blank values  caused by relationship and with no matched record. But I want to replace it with "Not assigned" text

[v-veshwara-msft · Accepted solution]

Hi @marbi ,
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.

[marbi]

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

[v-veshwara-msft]

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.

[Kedar_Pande]

@marbi 

Create a new column:

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

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[marbi]

not working. Acct Assignment Order - Text field & Purchasing Document Key is coming from different table. They are related using Order Key field. When I created the relationship, it shows blank values due to unmatched record. 

[Uzi2019]

Hi @marbi 

check this video for better understanding.

https://www.youtube.com/watch?v=dp0W8B6FAmc

https://www.youtube.com/watch?v=tl92KE_3DMw

https://www.youtube.com/watch?v=rqO0kx1P1VU

I hope above videos would help you!

[Uzi2019]

Hi @marbi 

Can you share the PBIX file and remove critical data from it??

[Bibiano_Geraldo]

Hi @marbi , 

What column do you want to replace with "Not assigned"? are you using dax? show us the DAX used, give more context, just to understand your problem.

[marbi]

I'll be replacing Acct Assignment Order - Text field to "Not assigned". When I created the relationship, it shows blank values due to unmatched record. I tried using below DAX, but it's not working

Column = IF('CVM_Acct Assignment Order - Text'[Acct Assignment Order - Text] = BLANK(),"Not assigned",'CVM_Acct Assignment Order - Text'[Acct Assignment Order - Text])

[Bibiano_Geraldo]

Its possible to share no sensitive file to see the problem closer?