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
android1
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

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
KHorseman
Community Champion
Community Champion

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!




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?

 

 

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!




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

Anonymous
Not applicable

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
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.