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

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

All - I'm seeking assistance with creating a calculated column that may require storing values in variables, which is not something I have experience with.

 

I need to create a calculated column that first looks in the "Type of Project" column in the first table below to see if it's an IT or non-IT project. From there, apply the "Approval Levels" rules listed in the second table, and return the appropiate value (Local, IOC, or Management) in the "Approval Level" column in the first table.

 

Major Kudos to anyone who can help me here...

 

Type of ProjectProject AmountApproval Level
AreaBudget 
IT$1,000,000 
HR$2,000,000 
Sales$3,000,000 
   
Approval Levels
IT projects>$1,499,999IOC
">$1 and <= $1,499,999Local
non IT projects>=$2,500,000IOC
">=$2,000,000 and < $2,500,000Management
"<$2,000,000Local
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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
 
ApprovalLevels.PNG
 
 

View solution in original post

Anonymous
Not applicable

@bchager6 - 

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

 

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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
 
ApprovalLevels.PNG
 
 

@AnonymousI just did some further testing of this code. While it works on the row level, so to speak, it doesn't look to be aggregating the budget dollars across the same project ID. I would expect to see IAC level for each row in the below table because the second row takes the project in to that approval level. Something I noticed is that if I remove the Project Name from my table, the individual project amounts aggregate and the correct approval level is returned. That isn't an optimal solution though because I need to include the project name in my table. Your thoughts?

 

Capture.JPG 

Anonymous
Not applicable

@bchager6 - 

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

 

Brilliant. That did the trick. Thank you SO much!

Yes, each project ID can have multiple project names.

Anonymous
Not applicable

Happy to help!

Incidentally we could have also done ALLEXCEPT(Project[Project ID]) instead of the ALL(Project[Project Name]).  Hindsight being 20-20 you may want to use that in the event you add a separate column other than Project Name that can have different values within the same Project ID.

Eric

 

Thank you again. You've been extremely helpful!

Thanks very much! Impressive, and fast!

I have a few quick questions: the budget numbers are already in the data. It looks like you are you saying that I should add the 2 additional columns to the data (MinBudget & MaxBudget) before I run the code?

 

Why is "IT" listed twice in this line? VAR _currentArea = IF(SELECTEDVALUE(Projects[Project Area]) = "IT", "IT", "Non-IT")

Anonymous
Not applicable

The VAR _currentArea could've been written IF(SELECTEDVALUE(Projects[Project Area] = "IT", SELECTEDVALUE(Projects[Project Area]), "Non-IT") but I got lazy.  🙂  I'm simply checking to see if I'm looking at a non-IT area for when we perform the "lookup" in the Approval Level table.

 

I'm not sure what your Approval Level table looks like (I'm assuming that it's a separate table).  Mine looks like the table on the right in the screenshot.  You simply want one column that has the numeric lower boundary of the approval level ladder and one column that has the upper boundary.  Have one column that says ">0 and <1000000" won't work for the way I created the solution.

 

Hope that helps!

Eric

 

Rookie question: Did you relate your approval level table to the source table?

Anonymous
Not applicable

Nope - it's just floating out there all by itself.  In fact you probably wouldn't want to so that any filters you applied to the budget table wouldn't flow thru the relationship to the approval level table.  And that M:M relationship could cause some headaches.

Anonymous
Not applicable

If this answered your question could you please mark as the Solution?

Thanks!

It's going to take a few days before I get to try this out, but when I do, absolutely.

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.