2 Tables and if(and logic

[Anonymous]

Hi All, 

I'm a bit stuck right now. I'm sure there's an easy solution, but it's evading me right now.

I'm trying to connect two tables with a relationship on customerid. I want to basically create a filter using this column.

Test = if(and(SALs[CPTCode]="NOBIL", SALs[DateOfService]=Appointment[AppointmentDate], 1, 0)

It seems I can't connect these two tables. It should be a 1:1 match on the dates. Am I using the wrong function?

Thanks!

[Anonymous]

Sorry, that was really a messy explaination. For further clarification, I have the following 3 tables:

ClientTable

CustomerID	Name	Primary Staff
1	Jack	Adam
2	John	Alex
3	Joan	Adam
4	Jill	Alex

Appointment Table

CustomerID	AppointmentDate	ApointmentStaff	ApptDesription
1	1/1/2019	Adam	OTHER
1	2/1/2019	Aubree	SAL
2	3/1/2019	Alex	OTHER
3	1/1/2019	Aubree	SAL
3	2/1/2019	Aubree	SAL
3	3/1/2019	Adam	OTHER

SALs Table

CustomerID	DateofService(AppointmentDate)	CPTCode	SALStaff
1	2/1/2019	NOBIL	Aubree
3	1/1/2019	NOBIL	Aubree
3	2/1/2019	1234	Aubree

I would like to make sure that all views of tables are related. So when I click on the 2nd row in the Sals table, the only option that shows up in the client table is Joan and the 4th row on the appointment table.

Right now, when I click on the second row in the SALs table, nothing is dynamically filtered on the client table. My relationships are currently all based on ClientID.

Thanks!

[Anonymous · Accepted solution]

For the time being you cannot have it the way you want without creating certain measures. This is because you want to filter tables based on not only CustomerID but also on dates and in PBI you cannot create relationships based on 2 columns in each table, only one can be used.

If you follow the star schema, then you should have at least 4 tables: the three you've shown and one proper Date table.

'Clients'[CustomerID] 1:* Appointments[CustomerID]
Clients[CustomerID] 1:* SALs[CustomerID]
Dates[Date] 1:* Appointments[AppointmentDate]
Dates[Date] 1:* SALs[DateOfService]

This is the right setup. If you now slice by customers in the Clients table and dates in the Dates table (the linked fields in fact tables should be hidden from the user), you'll get the right rows in both fact tables if you display them in a table.

Best
Darek

[Anonymous]

Your explanation is about as clear as mud...

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best
Darek