Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |