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

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

 

IDRegistered DateNamePhone Number
12/1/2019A32564
22/1/2019B2564
31/10/2019C98742
41/20/2019D369872
51/20/2019E369862
61/15/2019F3862

 

Interview Round table

 

IDInterview DateInterview Round Result
32/1/2019Tech 1selected
41/25/2019Tech 1selected
42/1/2019Tech 2rejected
52/1/2019Tech 1rejected
61/27/2019HRselected
22/1/2019Tech 1selected
2 ACCEPTED SOLUTIONS

@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:

 

image.png

 

 

View solution in original post

@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):

 

image.png

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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 

 

Anonymous
Not applicable

Thanks @AlB

 

I have some more requirement

 

  1. how many people attend an interview with current month registration (Ex: ID =2 registered on 02-2019 and attend on 02-2019) 
  2. 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]

 

data model.png

 

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:

 

image.png

 

 

@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):

 

image.png

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.