Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akim1
Regular Visitor

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
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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 

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
Not applicable

Could be, but nothing that I know of unfortunately.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.