topic How to replace blanks with a different value when connecting to a table in DAX Commands and Tips

How to replace blanks with a different value when connecting to a table

Anonymous — Mon, 30 Sep 2024 17:51:12 GMT

I have an excel file that lists various companies and a category they fall into ... IE Company A is Elite, Company B is Value, etc

I have two other tables with data that include a company code field, that links to the same code in that Excel file. However, the issue I am having is there are a lot of company codes that are not in the excel file. Currently, Power BI is returning blank for those. Ideally, I would like it to return 'Other' ...is there an easy way to do this? I can't add all company codes to the Excel file and create 'Other' in the category column because new codes are added every day

Re: How to replace blanks with a different value when connecting to a table

lbendlin — Mon, 30 Sep 2024 18:07:32 GMT

COALESCE([column],"Other")

Re: How to replace blanks with a different value when connecting to a table

Anonymous — Mon, 30 Sep 2024 19:02:35 GMT

I couldn't quite get that to work...where should I be adding that measure?

Re: How to replace blanks with a different value when connecting to a table

lbendlin — Mon, 30 Sep 2024 19:12:24 GMT

does it have to be a measure? A calculated column is likely enough.

Re: How to replace blanks with a different value when connecting to a table

Anonymous — Mon, 30 Sep 2024 19:49:57 GMT

it doesn't have to be a measure, but where would I put the calculated column? would i have to add it to both tables that are joined to the Excel file? I already have that field in there, but I would like to have a filter tie to both tables

Re: How to replace blanks with a different value when connecting to a table

AnnieTay — Tue, 01 Oct 2024 13:27:04 GMT

Assuming that Table1 is your Excel file, and Table2 contains the codes (ID) that are not present in Table1, you can add a calculated column to Table2:

ID_clean =
IF(
ISBLANK(
LOOKUPVALUE(
Table1[ID],
Table1[ID],
Table2[ID]
)
),
"Other",
FORMAT(Table2[ID], "General Number")
)

Re: How to replace blanks with a different value when connecting to a table

Anonymous — Tue, 08 Oct 2024 06:19:31 GMT

Thanks for the reply from lbendlin and AnnieTay.

Hi Anonymous ,

Have you solved your problem? Based on my testing, the code provided by AnnieTay does what you need:

This is the relationship I created:

You can also use the RELATED function, which does the same thing and doesn't need to have the fields in the Table:

Column = IF(ISBLANK(RELATED('Table'[Category])),"Other",RELATED('Table'[Category]))

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!