Calculated column that populates approval level based on project type and budget amount

So what I did to solve this was to change the model of the "Approval Levels" table to be Project Area, Min Budget, Max Budget, Approval Level.

Once I had that then I created an "Approval Level" calculated measure in the Projects table with the following code (you may need to adjust to match your model):

Approval Level =

IF(

HASONEVALUE(Projects[Project Area]) && HASONEVALUE(Projects[ProjectID]),

VAR _currentArea = IF(SELECTEDVALUE(Projects[Project Area]) = "IT", "IT", "Non-IT")

VAR _currentBudget = SUM(Projects[Budget])

VAR _Return = CALCULATE(MAX('Approval Levels'[Approval Level]), 'Approval Levels'[Project Area] = _currentArea, _currentBudget >= 'Approval Levels'[MinBudget], _currentBudget < 'Approval Levels'[MaxBudget])

RETURN _Return,

BLANK()

)

 

Since the appropriate approval level will be returned if I'm looking at one project (i.e. you probably don't want to aggregate budgets over all projects and then look-up the approval level but if you have multiple budget lines for one project it'll aggregate those amounts) the HASONEVALUE check verifies that we're looking at one project and one project area, stores those two values in variables, and then the CALCULATE statement filters the Approval Levels table down to that row that matches the Project Area and the budget amount is between the Min and Max Budget fields.  Since I know that I'm only looking at one row I can safely use MAX to retrieve the text value.

 

I can't figure out how to include the sample PBIX I created to solve this - if someone knows how to attach a PBIX let me know and I'll upload.  I've included a screenshot of the model and the DAX.

 

Hope that helps.

Eric

 

 

 

Anonymous - 

Am I reading the data correctly that one Project ID (in this case 540) can have multiple Project Names?  It looks like it has 4 separate names for that one Project ID.  Or did you type in the 1, 2, 3, 4 for illustrative purposes?

If it can have multiple names for one ID try changing the code for _currentBudget (I think that's the variable - it's not showing me the original post) from SUM(Project[Budget]) to CALCULATE(SUM(Project[Budget], ALL(Project[Project Name])).  I haven't tested that but that essentially "ignores" the project name but still honors the project ID.

I'll see if I can find my original PBIX and try to dummy it up.

Eric