Better solution: custom function, sql or table manipulation

[DSgUY]

Hi, I´m need some advice for a problem that I´am trying to solve. I learn the basic from SQL and PowerBI  and managed to solve some pieces but I need directions to sum all together.

What I have:

I have 3 tables on a SQL database of a medical institution.

• Number 1 is the schedule with patient name, doctor name, date, hours and service and treatment code (numbers).
• Number 2 have the service code (number) and a one word description of the service (char)
• Number 3 have the treatment code (number) and a one word description of the treatment (char).

What I need:

• Filter result between dates AND some condition (Example: Dr. Name).
• Filter custom results. Example, we want to get for a same date and hour and Dr. the schedules but only for a subset of the services.
• Display result and easy change a parameter from a panel or input box. Example: dates

What I did:

I manage to do this in SQL. For example for the second, I used INNER JOIN of the tables and do a AND NOT EXIST of another SELECT). 

What I Tried in PowerBI:

I tried to do my SQL query from POWERBI. I success at first but then I have some errors: SQL to long and column error.  (my SQL query is

Then I import the tables with all the data and do a first join on power bi but i get stuck on how i do the custom function.

Same as before but I tried to do a function that use my SQL query but with the tables that I already import to POWERBI.

I need someone to point me out a the direction to solve this and what is the best approach. I will do the rest.

Regards....and thanks!

[MFelix]

Hi @DSgUY ,

Taking into account your description, I assume that the tables 2 and 3 have unique identifiers, based on this you should make the sql link to each one of the tables on PBI.

After that you should create the relation between the table 2 and table 1 and table 3 and table 1 this will give you the called star schemma and then you can make your filtering based on this relationships. 

You should also add a calendar table to link to table one to have your time related calculations.

Check the link below about the relationship creation on PBI.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

Regards,

MFelix

[DSgUY]

@MFelix thanks for your fast reply!

I'm immediately start using your tips and work for hours to get what is next....

What I have done:

As you can see I have:

• A table with the data of the 3 tables (left table)
• Relations working.
• A slide that filter both tables.
• A total card that count the rows in SCHEDULE TABLE.
• A second table that filters the STATUS=FREE from SCHEDULE (right table). I use filters from right panel.

Stuck in:

• I want to make a card that counts the number of rows of the table of the right. I suppose that I can use functions measures to get this? Can I do a measure that count the rows in the pink table? 
• I want a third table and a card that only show (and count) the schedules where totaly FREE. That is that STATUS=FREE and for the same Day, Hour, Minutes, Name and Surname I dont have another schedule with STATUS=CANCELED or STATUS=FINISHED.
• An example from above is highlighted in the pic: Olivia Golden in the 25/07/2018 at 15:30 was not FREE because at the same date and time she finished a schedule. 

Iinks used:

• https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
• https://www.youtube.com/watch?v=F0OJOBrQtSA
• https://youtu.be/-4ybWQSRcOY

All the data shown above was created by me to simulate what I want to do and is totally random and nonsense ....

Regards.