Dax Formula Help

[akim1]

Hi Community,

I need some help to create a Dax formula. As you can see in the table below, in some rows, in my source data, are returning blank in the company name. I'm thinking to create a new column with a condition like: if the company name is blank, look up the same company code and the currency "USD" then return the company name from that row. Can anyone write this in DAX formula so I can put it in Power query in PowerBI?

	Company Code	Company Name	Currency
Row1	3001	Malaysia	USD
Row2	3001		Local

[Anonymous · Accepted solution]

HI @akim1 ,

You can try to use below calculate column formula if it suitable for your requirement:

Condition column =
IF (
    Table[Company Name] <> BLANK (),
    [Company Name],
    CALCULATE (
        LASTNONBLANK ( Table[Company Name], [CompanyName] ),
        FILTER (
            ALL ( Table ),
            [Comany Code] = EARLIER ( Table[Company Code] )
                && [Currency] = "USD"
        )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

You could create a helper table like this:

helper_table = FILTER(SUMMARIZE(original_table, 'original_table'[Company Code], 'original_table'[Currency], 'original_table'[Company]), 'original_table'[Company] <> "")

This will give you unique rows from your original table where the company is not missing. Then create a calculated column in this new table: code_currency = 'helper_table'[Company Code] & 'helper_table'[Currency]

Then in your original table you can do the lookup: 

company_revised = LOOKUPVALUE('helper_table'[Company], 'helper_table'[code_currency], 'original_table'[Company Code] & 'original_table'[Currency]

If you upload your file I can show you 

[akim1]

Thank you for the reply. Do you think if there is any way to do this without creating an additional table? I want to make this simple as possible.. 

[Anonymous]

Could be, but nothing that I know of unfortunately.