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
Prasanthh60
Regular Visitor

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
Super User
Super User

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




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

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


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

 

Prasanthh60_2-1718286804795.png

 

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.