cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
katielynne Frequent Visitor
Frequent Visitor

2 Tables and if(and logic

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!
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 2 Tables and if(and logic

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
3 REPLIES 3
Super User
Super User

Re: 2 Tables and if(and logic

katielynne Frequent Visitor
Frequent Visitor

Re: 2 Tables and if(and logic

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

 

ClientTable

CustomerIDNamePrimary Staff
1JackAdam
2JohnAlex
3JoanAdam
4Jill

Alex

 

Appointment Table

CustomerIDAppointmentDateApointmentStaffApptDesription
11/1/2019AdamOTHER
12/1/2019AubreeSAL
23/1/2019AlexOTHER
31/1/2019AubreeSAL
32/1/2019AubreeSAL
33/1/2019AdamOTHER

 

SALs Table

CustomerIDDateofService(AppointmentDate)CPTCodeSALStaff
12/1/2019NOBILAubree
31/1/2019NOBILAubree
32/1/20191234Aubree

 

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!

Super User
Super User

Re: 2 Tables and if(and logic

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 40 members 1,066 guests
Please welcome our newest community members: