Measure doesn't recognise the relationship (Number of Applicants by recruitment stage by period)

[SLiakhovskaia]

UPD:  dummy data and report files are added (see the links down below)

After three days of trial and error I would want to borrow some wisdom from the community.

Scenario:

Applicants are transitioned through the recruitment stages. I want to calculate the number of open/close applications for each period (day, month, quarter) by picking up the most latest recruitment stage they are at.

The fact table holds Applicant ID, stage name and the transition date. This date linked to the date table and reflects the day they were transitioned to the stage (rather than the actual event date). Sometimes the recruiter has to rush the candidate through the stages which results in multiple transitions per date. There is no unique ID in this table (there is applicants list sitting separately which this table is linked to).
There is a dimensional table that holds the stages list along with the stage logical sequence number (1 - Applied, 20 - Hired).

To identify the latest transition record I thought to find the max stage sequence number for max date transition date. The other scenario I thought of was to rank transitions by applicant and date (equivalent of SQL row number with partitioning).

Any combinations with RANKX or MAX has got me odd results like there was no relationship between fact table and dimension (I was getting Cartesian produts of transitions and stages).
CROSSFILTER and GROUPBY helped (sort of) but the measures turned to be too prone to any filter changes and look clunky.

My gut feeling was that I need to improve my tables and/or data model as I am trying to go upstream to pick up the stage sequence number from dimentional table and bring it back to the fact table. 

• Is it better to add a column with sequence number to the transitions fact table in Power Query?
• Should I add a calculated ID column to the fact table?
• Would I better set the relationship as bi-directional?
• What formula scenario should I go with?
  
  

Max Transition Seq = 
// It works (almost), I just wanted to understand why it doesn't recognise the relationship (how to shape this measure to make it work without Crossfilter). 
CALCULATE( MAX( 'Apps Progression Stages'[Sequence] ),
ALLEXCEPT( 'Apps by Stage Progression', 'Apps by Stage Progression'[Application ID],'Apps by Stage Progression'[Date First Transitioned] ), 
VALUES('Apps by Stage Progression'[Application ID] ),
CROSSFILTER('Apps by Stage Progression'[App Transition Status Name (sequenced)], 'Apps Progression Stages'[App Stage (sequenced)], both ) )

Max Transition Date = 
// works but I'd expect it to work with 'allexcept'. Replacing groupby with allexcept results in Cartesian product
CALCULATE( MAX( 'Apps by Stage Progression'[Date First Transitioned] ), 
GROUPBY( 'Apps by Stage Progression', 'Apps by Stage Progression'[Application ID] ) )

Transitions by Applicant by Date = 
// works when both Applicant ID and Date First Transitioned are present in context but only shows the total transitions per applicant when there is no date filed added to context. 
// I wanted it to show transitions for the max transitioned date (period-wise)
CALCULATE( COUNTROWS( 'Apps by Stage Progression'), 
ALLEXCEPT( 'Apps by Stage Progression', 'Apps by Stage Progression'[Application ID],'Apps by Stage Progression'[Date First Transitioned] ) )

Lookup for Transaction seq = 
// works but only where there is one transition per table visual row
LOOKUPVALUE( 'Apps Progression Stages'[Sequence] , 'Apps Progression Stages'[App Stage], SELECTEDVALUE('Apps by Stage Progression'[App Transition Status Name] ) )

Report file  Dummy data

[v-xiaotang]

Hi @SLiakhovskaia 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

The new INDEX function allows you to perform SQL-like partitioning, By using both date and sequence number as the order by columns you can get the last row for each applicant.

[SLiakhovskaia]

Happy Ney Year!

I'm back at my desk and looking at the new INDEX function. With my every attempt it keeps saying that

"INDEX's Relation parameter may have duplicate rows. This is not allowed."

The table with application transitions 100% doesn't have duplicated rows, there is added (in PQ) index column and this index column is set up as primary key. I broke my head trying to figure out how to mitigate this error.

The closest attempt was as below:

Max Transition ID_INDEX = 
    INDEX( 1, 
        SELECTCOLUMNS( NATURALLEFTOUTERJOIN('Apps by Stage Progression', 'Apps Progression Stages') , 
            "Transition ID", [Transition ID], 
            "Application ID", [Application ID] , 
            "Date First Transitioned", [Date First Transitioned], 
            "App Progression Stage", 'Apps by Stage Progression'[App Progression Stage],
            "Sequence", [Sequence]), 
        ORDERBY(
            [Application ID] , ASC,
            [Date First Transitioned], DESC, 
            [Sequence], DESC, 
            [Transition ID], DESC), 
        KEEP, 
        PARTITIONBY([Application ID] ) )

[johnt75]

Jeffrey Wang mentions some restrictions where that error might be returned. You may be stuck using the TOPN approach.

[SLiakhovskaia]

Thank you for this clue. I'm on the older version, unfortunately, and it will take a while to get it upgraded.

Any chance you could suggest the measure syntax to find the latest transition record as at end of period for each Applicant ID (Applicants table) by transition date (Transitions table) and max sequence number of the transition stage (Stages table). I'm going to add unique ID column to Transitions table.

[johnt75]

You can try

latest record = 
IF( HASONEVALUE( 'Applications'[Application ID]),
	VAR ReferenceDate = MAX( 'Open Dates'[Date])
	VAR LastTransaction = CALCULATETABLE(
		TOPN( 1, 'Apps by Stage Progression', 'Apps by Stage Progression'[Date First Transitioned], DESC,
			'Apps by Stage Progression'[App Transition Status Name (sequenced)], DESC
		)
	)
	VAR TxID = SELECTCOLUMNS( LastTransaction, "@val", 'Apps by Stage Progression'[Transition ID])
	RETURN TxID
)