Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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!

Anonymous
Not applicable

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
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors