Getting the price of service from a different table : Please help!!

[Aditya_K1999]

I have a table with the below columns:

1) Service ID
2) Service Name
3) Pet Type
4) Pet Size
5) Service Charge

The above table is named as "Service Details" which basically acts as a database for all the services offered.

Then there is a table by the name of Service Availed. Where I want the service charge to be auto populated based on the size of the pet, type of the pet.

What formula to be used?

[baba_sawane]

Hi,

try :

If in your Service Availed table, you have columns where user select pet type & pet size then add a virtual column with this formula : 

any(select(Service Details[Service Charge],and([Pet Type] = [_thisrow].[Pet Type], [Pet Size] = [_thisrow].[Pet Size]) )

it will select the row value of [service charge] in Service details table that match the two criterias.

If it's not how your app is supposed to work, please explain further the app purpose's.

Hope it helps !

[Aditya_K1999 · Accepted solution]

Thank you for your response.

any(select(Service Details[Service Charge],and([Service Name] = [_thisrow].[Service Name].[Service Name], [Pet Size] = [_thisrow].[Booking ID].[Pet Size]) ))

I tried the above formula. First I am trying to match the service name, then the pet size. But the result of the same is coming as blank/0.

[Aditya_K1999]

I understood, your formula did work, Thank you.

However, wanted to know one thing. My database in the backend is like:

Because of which in my front end also, in the dropdown for service name it is showing the a particular service 4 times. I want it to show only once..

[baba_sawane]

Hi,

please use the reply button for better understanding. 

In your service name column, you can use unique(list) in your suggested values formula to select unique values.

For example : unique(yourtablename[Service name])

Did the formula I suggested worked ? If so you can mark it as solution

Have a nice day !

[Aditya_K1999]

It is only taking service the service name. If i put service name in the formula its shows a yellow triangle but if I use service ID it works fine, but then it doesn't show unique value..