topic Re: Create a column that returns value of another column while keeping the original value if no matches in DAX Commands and Tips

Create a column that returns value of another column while keeping the original value if no matches

ESG — Mon, 01 Apr 2024 08:04:39 GMT

Hi all,

I'm completely new to PowerBI and have been exploring posts on the community board, but I can't seem to find a solution to my query and I really need help.

I have a table with 3 companies ranging across year 2022 and year 2023.

I am trying to insert a new column called 'No. of customers at investment', which is actually the number of customers at start of year for 2022:

How do I create a column that would return the value of 'No. of customers at start of year' for year 2022, regardless of whether the 'Year' column is 2022 or 2023?

Many thanks in advance!

Re: Create a column that returns value of another column while keeping the original value if no matches

Jihwan_Kim — Mon, 01 Apr 2024 08:24:23 GMT

Hi,

I am not sure how your semantic model looks like but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

# customers at investment: = CALCULATE ( SUM ( data[#_customers_start_of_year] ), 'year'[year] = 2022 )

Re: Create a column that returns value of another column while keeping the original value if no matches

Anonymous — Tue, 02 Apr 2024 01:54:31 GMT

Hi ESG ,

Your solution is great, Jihwan_Kim . It works like charm! Here, I have another idea, I want to share it for reference. I used the calculation column instead of Measure.

Investment = CALCULATE (Max ('Table' [Start]), Filter (all ('table'), 'table' [year] = 2022 && 'table' [company name] = Earlier ('table' [company name]))

The final result is shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create a column that returns value of another column while keeping the original value if no matches

ESG — Tue, 02 Apr 2024 02:12:47 GMT

thank you very much Jihwan_Kim and Anonymous for your guidance!