Forum Discussion
how to avoid circular reference in datamodel
Hi all,
I have two tables Interview Registered Table and Interview Round table
if I select 02/01/2019 in date filter,I want to show following requirment
- No candidate registered = 2 (ID = 1,2)
- No of candidate interview attend = 4 (ID =3,4,5,2)
Note: currently I have mapped by ID for other requirements
Interview Registered Table
| ID | Registered Date | Name | Phone Number |
| 1 | 2/1/2019 | A | 32564 |
| 2 | 2/1/2019 | B | 2564 |
| 3 | 1/10/2019 | C | 98742 |
| 4 | 1/20/2019 | D | 369872 |
| 5 | 1/20/2019 | E | 369862 |
| 6 | 1/15/2019 | F | 3862 |
Interview Round table
| ID | Interview Date | Interview Round | Result |
| 3 | 2/1/2019 | Tech 1 | selected |
| 4 | 1/25/2019 | Tech 1 | selected |
| 4 | 2/1/2019 | Tech 2 | rejected |
| 5 | 2/1/2019 | Tech 1 | rejected |
| 6 | 1/27/2019 | HR | selected |
| 2 | 2/1/2019 | Tech 1 | selected |
Anonymous
Don't create that additional relationship between the IDs. Keep the relationships we already had.
For the first measure try this:
1. On the Date table, add a column with the month number so that you have [Month] for month names and [Month number] for the numbers.
2. Place 'Date'[Month] in the rows of a matrix visual.
3. Create this measure for the count of people attended and registered in the current month
RegAndAttThisMonth = VAR _IdsRegisteredThisMonth = DISTINCT ( 'Interview Registered Table'[ID] ) VAR _IdsAttendedThisMonth = DISTINCT ( 'Interview Round table'[ID] ) RETURN COUNTROWS ( INTERSECT ( _IdsAttendedThisMonth; _IdsRegisteredThisMonth ) )4. Place the measure in values of the matrix visual. You'll get something like this:
Anonymous
Now for the second measure try this:
1. and 2. exactly as above (already done)
3. Create this measure for the count of people that attended this month but registered in the previous month:
RegLastMonthAttThisMonth = VAR _IdsRegisteredLastMonth = CALCULATETABLE ( DISTINCT ( 'Interview Registered Table'[ID] ); ALL ( 'Date' ); FILTER ( ALL ( 'Date'[Month Number] ); 'Date'[Month Number] = SELECTEDVALUE ( 'Date'[Month Number] ) - 1 ) ) VAR _IdsAttendedThisMonth = DISTINCT ( 'Interview Round table'[ID] ) RETURN COUNTROWS ( INTERSECT ( _IdsAttendedThisMonth; _IdsRegisteredLastMonth ) )4. Place the measures in values of the matrix visual. You'll get something like this (with both measures):
4 Replies
Hi Anonymous
I think it would be best to start by
1. Create a standard Date table
2. Create a 1-to-many unidirectional relationship between Date[Date] and 'Interview Registered Table'[Registered Date]
3. Create a 1-to-many unidirectional relationship between Date[Date] and 'Interview Round Table'[Interview Date]
4. Place Date[Date] in a slicer where you'll select the day you want.
5. Place these two measures each in a card visual:
NumCandidatesRegistered = DISTINCTCOUNT('Interview Registered Table'[ID])NumCandidatesAttendedInterview = DISTINCTCOUNT('Interview Round Table'[ID])This will give you the numbers you need. If you also need the list of IDs, Names that registered or attended interviews we'll need to do something else. I'm not sure if you do need that.
What do you mean by this exactly?
Note: currently I have mapped by ID for other requirements
Thanks AlB
I have some more requirement
- how many people attend an interview with current month registration (Ex: ID =2 registered on 02-2019 and attend on 02-2019)
- how many people attend an interview with previous month registration (EX: ID =3,4,5 registered on 01-2019 and attend on 02-2019)
How can I achieve this logic?
for that reason, I have created the relationship between 'Interview Registered Table' [ID], 'Interview Round Table' [ID]
is it possible to create two relationships (ID & Date) between two table? I hope it will create a circular loop between 'Interview Registered Table', 'Interview Round Table', master calendar
Anonymous
Don't create that additional relationship between the IDs. Keep the relationships we already had.
For the first measure try this:
1. On the Date table, add a column with the month number so that you have [Month] for month names and [Month number] for the numbers.
2. Place 'Date'[Month] in the rows of a matrix visual.
3. Create this measure for the count of people attended and registered in the current month
RegAndAttThisMonth = VAR _IdsRegisteredThisMonth = DISTINCT ( 'Interview Registered Table'[ID] ) VAR _IdsAttendedThisMonth = DISTINCT ( 'Interview Round table'[ID] ) RETURN COUNTROWS ( INTERSECT ( _IdsAttendedThisMonth; _IdsRegisteredThisMonth ) )4. Place the measure in values of the matrix visual. You'll get something like this: