Forum Discussion
Complex Dax use case
I have this complex use case, I need to get a list of projects or flag them in DAX based on the following:
- If a project has multiple expense types then flag :
The project that has 5% or more in variance in more than one expense type but variance in $ is within 85k for those expense types.
- if project has one expense type then flag it when variance is above 5% but variance in $ is less than 85K.
In the table below the projects MNO and EFG will be flagged because they fall within that criteria, project ABC won't because one of it's variance that is above 5% is also above 85K
I used the following formla to flag the ones that have more than one expense type:
Column = Var __MultiExpenseType= CALCULATE(DISTINCTCOUNT('Projects Summary'[Expense Type]), ALLEXCEPT('Projects Summary','Projects Summary'[Project])) RETURN __MultiExpenseType > 1
Any help will be appreciated!
9 Replies
what are you struggling specifically, bringing all the requirements together? also can you provide the data in text format not a screenshot.
The part I am strugging with is when the project has 2 or more expense categories with 5%, I am able to get a result if it's just one expense category bigger than 5%:
Final flag = if([Flag with multi expense] =TRUE(),if(Varince %>5 && Variance in $ <85000,"Y","No"),"No")Project
Date
Expense Type
Variance in $
Variance in %
ABC
4/3/2023
X
93000
6%
ABC
3/3/2023
Y
70000
10%
ABC
4/3/2023
Y
5000
1%
KLM
2/3/2023
Z
12000
2%
MNO
4/3/2023
X
6500
7%
EFG
2/3/2023
Z
50000
10%
EFG
4/3/2023
M
60000
12%
EFG
3/3/2023
Y
1000
3%
as i am going through this i am little confused about your conditions;
for multiple rows must all expenses associated to that project be >= 5% or just >5% and < 85000 or <=85000
for a single row is it > 5% and < 85000?
I am trying to under why these 2 rows dont qualify for flagging.
ok i think i managed to figure it out, there might might be a simpler way of doing it but here it is.
i created a calculated column
CriteriaCol =
VAR result =
CALCULATE (
SUMX (
VALUES ( 'Projects Summary'[Project] ),
VAR varperc =
MAX ( 'Projects Summary'[Variance in %] )
VAR varcurr =
MIN ( 'Projects Summary'[Variance in $] )
RETURN
IF ( varperc > 0.05 && varcurr < 85000, 1, 0 )
)
)
RETURN
resultthen i created the flag
Flag =
VAR criteria =
SUMX (
VALUES ( 'Projects Summary'[Project] ),
VAR project =
SELECTEDVALUE ( 'Projects Summary'[Project] )
VAR expensetype =
SELECTEDVALUE ( 'Projects Summary'[Expense Type] )
RETURN
CALCULATE (
MIN ( 'Projects Summary'[CriteriaCol] ),
ALL ( 'Projects Summary' ),
'Projects Summary'[Project] = project
&& 'Projects Summary'[Expense Type] = expensetype
)
)
RETURN
criteriasee file attached
thank you so much for putting the all this effort into it, I followed your solution step by step but when I created the flag column I got an error of circular dependency because I am using the criteria column (Var Criteria Threshold), any ideas why I am getting this error?
it depends on how you are creating your data, what other calculated columns do you have in your data?
so my table has the following columns
Date, Project, Actuals, Current Budget, Expense Type, Var to budget ($) and Var to budget %both the Var to budget $ and % are calculated
Var to budget ($) =if('Projects Summary'[Actuals] <> blank(), ABS('Projects Summary'[Current Budget]-'Projects Summary'[Actuals]),blank())
Var to budget (%) = ABS(DIVIDE('Projects Summary'[Var to Budget],'Projects Summary'[Current Budget]))
But the error I get is that circular dependency between the 2 new columns that you suggested I create, could it be because the % column depends on the $ column?
