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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
audstrich
Regular Visitor

Problem with nested conditional column

I have had a very frustrating problem with this...

 

I have four columns that are involved in the conditional column I wish to make:

 

Insurance/Service Date/Claim Date/Payment Date.

 

If they haven't paid yet, I want the column to give me the days to code, from service date to claim date.

If they have paid, I want the column to give me the days it took to pay, from claim date to payment date.

BUT if they have paid and the Insurance is "Patient," then they probably paid on the date of service, not the claim date, so I don't want a negative number, and I would like the column to give me the days to pay from service date to payment date.

 

Days to Code or Pay = IF(ISBLANK('Billing and Collections'[Payment Date],1.*('Billing and Collections'[Claim Date]-'Billing and Collections'[Service Date]),IF('Billing and Collections'[Insurance Group]="Patient",1*('Billing and Collections'[Payment Date]-'Billing and Collections'[Service Date]),1*('Billing and Collections'[Payment Date]-'Billing and Collections'[Claim Date])))

 

This formula didn't give me the correct value for some of the accounts. I've also tried to make columns for each step, use datediff instead, and just do it in excel and import only the values to power bi. It is very strange- in query editor, it seems to give correct values. but when I go to the Desktop, the values are sometimes wrong. The problem seems to be in the days to pay area. Is it because I am referencing the insurance column which has a text value? Or are the formats of my date columns not the same possibly? Or do I just need to download the most recent update? This has been such a headache! Any help would be greatly appreciated.

2 ACCEPTED SOLUTIONS
jthomson
Solution Sage
Solution Sage

What does this do:

 

ubercolumn = 

var servicetoclaim = datediff([service date],[claim date])

var claimtopayment = datediff([claim date],[payment date])

var servicetopayment = datediff([service date],[payment date])

return if ([payment date]=blank(),servicetoclaim,if([Insurance]="Patient",servicetopayment,claimtopayment))

View solution in original post

v-juanli-msft
Community Support
Community Support

Hi @audstrich

You coluld add a calculated column in Power BI Desktop with DAX function instead of creating it in excel.

ubercolumn = 
var servicetoclaim = datediff([Service Date],[Claim Date],DAY)
var claimtopayment = datediff([Claim Date],[Payment Date],DAY)
var servicetopayment = datediff([Service Date],[Payment Date],DAY)
return if ([payment date]=blank(),servicetoclaim,if([Insurance]="Patient",servicetopayment,claimtopayment))

5.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @audstrich

You coluld add a calculated column in Power BI Desktop with DAX function instead of creating it in excel.

ubercolumn = 
var servicetoclaim = datediff([Service Date],[Claim Date],DAY)
var claimtopayment = datediff([Claim Date],[Payment Date],DAY)
var servicetopayment = datediff([Service Date],[Payment Date],DAY)
return if ([payment date]=blank(),servicetoclaim,if([Insurance]="Patient",servicetopayment,claimtopayment))

5.png

 

Best Regards

Maggie

jthomson
Solution Sage
Solution Sage

What does this do:

 

ubercolumn = 

var servicetoclaim = datediff([service date],[claim date])

var claimtopayment = datediff([claim date],[payment date])

var servicetopayment = datediff([service date],[payment date])

return if ([payment date]=blank(),servicetoclaim,if([Insurance]="Patient",servicetopayment,claimtopayment))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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