Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
Proud to be a Super User!
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.
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.
Proud to be a Super User!
Ok. Thanks for your help. I'll see how I get on using filters.
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |