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?

     

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    3 years ago

    it depends on how you are creating your data, what other calculated columns do you have in your data?  

    • Rachel_123's avatar
      Rachel_123
      Helper I
      3 years ago

      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?
    • Rachel_123's avatar
      Rachel_123
      Helper I
      3 years ago

      So I created a table exactly like the example I posted in my question, with just these columns and no additional calculated column , I implemnted your solution and I am still getting circular dependency , I am not sure if I am doing somethign different than you 😞