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 c...
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

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.