cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

New Column based on values in 2 other columns

Hi,

 

I have a table called Users with columns Type & Name. I need a new column called Male which will take all names in Name only if

their corresponding value in Type is Male.

 

Should look something like this

 

${}-Book1 - Microsoft Excel.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: New Column based on values in 2 other columns

Well, the equivalent third column would be

 

Female = IF(Table[Type] = "Female", Table[Name], BLANK())

 

As with the Male category it would just be blank on male rows.

 

You couldn't accomplish this - where "this" means that example thing you made in Excel - with filters. But presumably you want to use this column for something. Whatever that something is you could probably do it with filters. How to accomplish that something depends entirely on what that something is.* As a trivial example: if you wanted a list of male names, you could just add a table or matrix visual with Name in the rows slot, then add Type to the Visual Level Filters box and check "Male" in that filter.

 

*Good Lord that sentence probably killed 10 non-English speakers trying to decipher this thread. And probably 5 English speakers.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Highlighted
Super User I
Super User I

Re: New Column based on values in 2 other columns

The way you've drawn up your example would not work. Adding a column to an existing table would still use that table's row structure. You have Mark's name on Mary's row. That's impossible. What data on Mary's row would allow a formula to look up Mark's name? At best you'd have to show a blank on that row. If having blank rows is acceptable you could add a column with the formula:

 

Male = IF(Table[Type] = "Male", Table[Name], BLANK())

 

But depending on what you're actually trying to do with this in the end you may not even need an extra column. It's possible that you could accomplish your goal using filters, but that's hard to tell from the scope of your question.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
Post Patron

Re: New Column based on values in 2 other columns

Hi,

 

I think you're right. I was then looking to add a column called Female but can't if I use Blank().

How could I achieve this with a filter?

 

 

Highlighted
Super User I
Super User I

Re: New Column based on values in 2 other columns

Well, the equivalent third column would be

 

Female = IF(Table[Type] = "Female", Table[Name], BLANK())

 

As with the Male category it would just be blank on male rows.

 

You couldn't accomplish this - where "this" means that example thing you made in Excel - with filters. But presumably you want to use this column for something. Whatever that something is you could probably do it with filters. How to accomplish that something depends entirely on what that something is.* As a trivial example: if you wanted a list of male names, you could just add a table or matrix visual with Name in the rows slot, then add Type to the Visual Level Filters box and check "Male" in that filter.

 

*Good Lord that sentence probably killed 10 non-English speakers trying to decipher this thread. And probably 5 English speakers.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Post Patron
Post Patron

Re: New Column based on values in 2 other columns

Ok. Thanks for your help. I'll see how I get on using filters.

Highlighted
Frequent Visitor

Re: New Column based on values in 2 other columns

Hi, I have been given an excel doc by a colleague they want translating into Power BI. They have used COUNTIF in excel, not sure if the same is required in PBI or not.

Essentially we have a single table, with a list of incident numbers and a reporting month. I have created a column (in same table) to define whether the reporting month is, "Current Month", "Previous Month" or "Historic". 

What I need to do now is calculate if an incident is still open using the following logic:
- Incident number exists in Current Month and Previous Month then a value of "Open" is returned
- Incident number does not exist in Current Month but exists in Previous Month then a value of "Closed in Period" is returned

Any help would be gratefully received!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors