Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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
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
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..
Could be, but nothing that I know of unfortunately.
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |