Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Project | Project Amount | Approval Level |
Area | Budget | |
IT | $1,000,000 | |
HR | $2,000,000 | |
Sales | $3,000,000 | |
Approval Levels | ||
IT projects | >$1,499,999 | IOC |
" | >$1 and <= $1,499,999 | Local |
non IT projects | >=$2,500,000 | IOC |
" | >=$2,000,000 and < $2,500,000 | Management |
" | <$2,000,000 | Local |
Solved! Go to Solution.
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):
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
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):
@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?
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.
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")
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?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |