cancel
Showing results 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

New Member

## Creating custom column based on 3 other colums

HI,

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 Type Sponsor Net Amt Custom colum IP Payer - Y 490 IP IP 47 OP IP 4200 IP OP Payer - Y 300 OP OP 25 OP OP 2100 OP
1 ACCEPTED SOLUTION
Super User

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

select your net amount then convert to whole number.

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

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"))

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

Super User

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!!!
New Member

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"))

Super User

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

select your net amount then convert to whole number.

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

It worked. Thank you so much.

Super User

Did u take column right not measure???

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

Yes sir.  Under Modeling - new column

Super User

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

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

You are basically there with how you outlined your reuqirements.

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

Hope this helps

Joe

Proud to be a Super User!

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

New Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors