cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions
Super User III
Super User III

Re: how to avoid circular reference in datamodel

@vengadeshpalani

 

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

Super User III
Super User III

Re: how to avoid circular reference in datamodel

@vengadeshpalani

 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
Super User III
Super User III

Re: how to avoid circular reference in datamodel

Hi @vengadeshpalani

 

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 

 

Re: how to avoid circular reference in datamodel

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 

Super User III
Super User III

Re: how to avoid circular reference in datamodel

@vengadeshpalani

 

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

Super User III
Super User III

Re: how to avoid circular reference in datamodel

@vengadeshpalani

 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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors