Fetch Value from Table 2 relations away

[Anonymous]

Hello!

I am having some trouble with my data.

I have three tables: Table1, Table2, Table3

Table2 contains a list of sessions containing information about sessions. Each session has a client ID, a session module and type of session, and a session ID. For a client, all appointments within the same module will have the same type.

Table1 contains the list of required sessions per module, separated per session type.

Table3 should contain a list of the clients + required sessions per module depending on the type of session they will be having for that module.

Table2:

ClientID	Session Module	Session Type	SessionID
1	1	A	1
1	1	A	2
1	2	A	3
1	3	B	4
2	1	A	5
2	2	B	6
2	2	B	7
2	3	B	8

Table1:

Session Type	Module1	Module2
A	3	5
B	4	6

DESIRED Table3:

Client	Module1	Module2
1	3	5
2	3	6

I have the following connections:

Table1[Session Type] is connected to Table2[Session Type]

Table2[Client ID] is connected to Table3[Client ID]

So the process is, for every client, for every module, I need to check the type of session the client will be taking that module. Based on the type of session for that module, I need to get the required number of sessions from Table3.

(The end goal is to check the percentage of sessions a client has done for each module, but the type of session can change from module to module and I haven't managed to assign the required number of sessions per client).

How would I go about doing this? Table3 and Table1 don't share a connection. 

Thank you in advance!

[Anonymous · Accepted solution]

Hi, for your case i've tranformed  your tables to make relationship between "Session Type+Module" columns.

Please note, for table1, i perform unpivot to "module1" and "module2" columns, after unpivot i've changed the values "module1" and "module2" to "1" and "2". The result of unpivot is "Session Module" Column, then i've created "Session Type + Module" column as result to concatenate "Session Module" and "Session Type" columns.

For table2 i've only concatenate "Session Module" and "Session Type".

The measures created was:

#Sessions Done = DISTINCTCOUNT(Table2[SessionID])

#Sessions Needed = SUM(Table1[Session Needed])

Sessions Done / Sessions Needed = IF(ISBLANK([#Sessions Needed]),BLANK(), [#Sessions Done]/[#Sessions Needed])

I hope to help you.

[Anonymous]

Thank you a lot!

That did solve my problem, thank you so much for the help.