Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
android11
Regular Visitor

Creating a new column if values in column 1 = column 2

Hi,

 

I am trying to create a new column that includes all names in column Fullname if they are equal 

to a certain value in column UserType.

 

If Mary Byrne in Fullname is of UserType "Caregiver", I want Mary added to a new column Caregiver.

Tried this with no luck -> Carergiver = IF( Users[FullName],[UserType]="Caregiver")

 

Carer Column.jpg

1 ACCEPTED SOLUTION
Neuro81
Helper I
Helper I

if you are doing this in Power Query then
this should do it
=if [usertype]="caregiver" then [Fullname] else ""

if you are creating a calculated column then its

Caregiver = if(users[UserType]="caregiver",users[Fullname],BLANK())

The first part of an IF is to check the logic so it checks to see if the first expression is true or false
then the second part is what is returned if its TRUE
and the last if it is false

-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --

View solution in original post

10 REPLIES 10
Neuro81
Helper I
Helper I

if you are doing this in Power Query then
this should do it
=if [usertype]="caregiver" then [Fullname] else ""

if you are creating a calculated column then its

Caregiver = if(users[UserType]="caregiver",users[Fullname],BLANK())

The first part of an IF is to check the logic so it checks to see if the first expression is true or false
then the second part is what is returned if its TRUE
and the last if it is false

-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --

If I use your Custom Column option and change caregiver to client & put these two columns in a table,

one column is always blank. How do I get both to display side by side?

if you have only two usertypes then yes one would always be blank
as you are checking in one column if one is a caregiver and in the other if it is a client.

In my mind I think what you are trying to do is show for any row who is the caregiver and who is the client.
if that is the case you would need to create some sort of relationship between the caregiver and the client

but please do correct me if I am mistaken and if you can provide your desired output (maybe display it in an excel screen shot)
and I will try and help you when im back online tomorrow morning

-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --

You're right, I am trying to show for any row who is the caregiver and who is the client.

This is what I would like ->

 

${}-Book1 - Microsoft Excel.jpg

Cool,

In order to get to the stage where you can match up a Client to the Caregiver, you will need something in your base table that actually shows this relationship.

Just looking at the columns [Full Name] , [User Type] 
We dont have any information to say that a Caregiver has a relationship to a client or vice versa.

Normally I would expect a table to look bit like this (at the minimum)
[Id] , [Full Name], [User Type], [Assgined Carer Id]

Other questions come to mind, like would you have a situation where you might have two carers associated to a single client?
would you have a change in carer and how do you keep track of the change?




-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --

I would have 2 or more carers assigned to a client. As for change of UserType, once someone

is a Caregiver, they are always a Carer. Same for Client.

pq reference.JPG

sorry Ive only had a chance to get back to you now

my first reaction would be to create two tables out of your users
one for Caregivers and one for users

to do that from your users table create a reference
give it a new name eg clients and filter out all the caregivers

 

 

 

 

pq relat.JPG

pq table 1.JPG now create relationships using the userid and the client id  / caregiver ids
and you have a small model which can refer to each other, this should give you most of what you are looking to do, let me know if you have any issues and I can help you out


-- ------------------------------ --
Please mark solutions as complete when your question has been answered
-- ------------------------------ --

Hi,

 

I have two tables, Users & Service Task. Users has 4 columns -> Fullname (a mix of carers & clients) , UserType(Client or Caregiver) and UserTypeID (1 = Caregiver, 2= Client) and UserID

 

Service Task table contains CareGiverID & Client ID

 

-Untitled - Power BI Desktop.png

 

 

Can you use matrix visualization like the following?

 

matrix.png

Perfect Neuro. Thank you very much. It was for a Calculated Column.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.