Forum Discussion

Rachel_123's avatar
Rachel_123
Helper I
3 years ago

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 ha...
Need Help
vanessafvg's avatar
vanessafvg
Community Champion
3 years ago

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
result

 

then 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
criteria

 

see file attached

 

Rachel_123's avatar
Rachel_123
Helper I
3 years ago

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?