Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 |
Solved! Go to Solution.
@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):
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 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:
@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):
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |