Create data validation with multiple datasets

[Anonymous]

Hello everyone,

I am new to the community and PBI. I have a question about creating a validation check to compare a generated reports against multiple datasets. Lets say:

I want to make sure an insurance company is reimbursing my practice fairly and according to the negotiated rates. I generate every 2 weeks a report for all the services provided during said period and the amounts reimbursed. Then I cross reference each value to the datasets I have for each payor (each unique payor has its own dataset). The way we do it now is by manually opening two or more excel files and referencing each payment, which can get tedious when there are thousands of rows. Is there a way to build a validation check for each service under each payor in which PBI would let me know when an amount is less than the negotiated amount in a more streamlined way? Lets say highlight the cell in red. 

Thanks!

[parry2k]

@Anonymous I would recommend creating a table for all validation data (against which you want to test your amounts), and I believe this validation data rarely changes. Add another table for your payments and set the relationship between these two tables and you can validate the information from there and conditional highlight for the discrepancy.

Although the above logic will work, if you are unable to implement it, share some sample data, and will able to provide the solution.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k  Thank you so much for your prompt response. I am still very new to all of this so I still have to do more self-learning. Lets use this as an example:

This would be the tables for two payors, which would be two separate excel files:

Florida Blue	Service provided	Amount Allowed
	12345 - EKG	$200
	23456 - Echo	$450
	33333 - MRI	$300

United Healthcare	Service provided	Amount Allowed
	12345 - EKG	$500
	23456 - Echo	$600
	33333 - MRI	$450

And now I generate a report for the services provided for the past two weeks, it would look a little like this after cleaning the data:

Service Provided	Payor	Amount Allowed
12345 - EKG	United Healthcare	$100
33333 - MRI	Florida Blue	$200
23456 - Echo	Florida Blue	$450

Now, I would want to highlight in red the cells where the data is less than the amount negotiated. And in practice this would apply to thousands of rows. Can I use the data from the preexisting tables instead of creating one table for all the validation data? And run the validation this way?

Thanks!

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

It seems that multiple table names appear in your expected result table, so it is suggested  to use "folder" connector to connect to the data source.If you use the "excel" connector, it is difficult to get the table name of each table, and it is difficult to integrate the data sources one by one.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.