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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Prasanthh60
New Member

Creating custom column based on 3 other colums

HI,

 

Please help me to create a custom colum based on 3 other colums.

1. if patient type is OP then custom colum should reflect OP.

2. If patient type is only IP and Sponsor is only blank and Net amount less than 1000 then OP others as IP.

 

basically, the custom colum should reflect as the below.

 

Patient TypeSponsorNet AmtCustom colum
IPPayer - Y490IP
IP 47OP
IP 4200IP
OPPayer - Y300OP
OP 25OP
OP 2100OP
1 ACCEPTED SOLUTION

Hi @Prasanthh60 

 

Your net amount column is text please convert into interger / whole number.

select your net amount then convert to whole number.

Uzi2019_0-1718287208324.png

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

View solution in original post

11 REPLIES 11
Uzi2019
Super User
Super User

Hi @Prasanthh60 

 

You can try below calculated column.

Custom new =
IF(Customer[Patient Type]="OP","OP", If(Customer[Patient Type]="IP" && Customer[Sponsor]=BLANK() && Customer[Net Amt]<1000,"OP","IP"))
 
Uzi2019_0-1718280091925.png

 

 

I hope i answered your question!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Thank for your input. 

But I am getting the below error. can you please help.

Prasanthh60_0-1718286182414.png

 

Hi @Prasanthh60 

 

share your formula also...
bcz by looking at error wont able to understand you formula mistake.

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Thanks in advance.

 

custnew = IF('12#06#2024'[Patient Type]="OP","OP", IF('12#06#2024'[Patient Type]="IP" && '12#06#2024'[Primary Bill Sponsor]=BLANK() && '12#06#2024'[Net Amt]< 1500, "OP", "IP"))
 
Prasanthh60_0-1718286683391.png

 

Hi @Prasanthh60 

 

Your net amount column is text please convert into interger / whole number.

select your net amount then convert to whole number.

Uzi2019_0-1718287208324.png

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

It worked. Thank you so much.

Hi @Prasanthh60 

 

Did u take column right not measure???

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Yes sir.  Under Modeling - new column

Hi @Prasanthh60 

 

error you are getting for type conversion it means your either of the column is text and comparing with numeric value<1000 or either of your column is numberi comparing with text value "OP" /"IP"..

 

Please check that..

I\

 

I hope it may resolved your issue!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
Joe_Barry
Solution Sage
Solution Sage

Hi @Prasanthh60 

 

You are basically there with how you outlined your reuqirements.

 

CustomColumn =
IF(Table[Patient Type] = "IP" &&
Table[Sponsor]= BLANK() &&
Table[Net Amt] < 1000, "OP",
IF(Table[Patient Type] = "OP", "OP", "IP")

 

Hope this helps

Joe

Thanks joe for your input. But i am getting the same error as below.

 

Prasanthh60_2-1718286804795.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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