Forum Discussion

akim1's avatar
akim1
Regular Visitor
6 years ago
Solved

Dax Formula Help

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 CodeCompany NameCurrency
Row13001MalaysiaUSD
Row23001 Local
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      akim1
      Regular Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Could be, but nothing that I know of unfortunately.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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