Complex PBI Dax Use Case

Question

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 this to identify which projects have multiple 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!

------------Update------------- someone suggested the creation of 2 columns,

Var Criteria Threshold = 
 Var __result= 
 CALCULATE (SUMX(
                    VALUES('Projects Summary'[Project]),
                    VAR __varPERC = MAX('Projects Summary'[Var to Budget %])
                    VAR __varCurr = MIN('Projects Summary'[Var to Budget])
                    RETURN 
                    IF(__varPERC> 0.05 && __varCurr <83000,1,0)
 )
 )
RETURN 
__result

and then create another one

flag projects for Variance threshold = 
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'[Var Criteria Threshold]),
        ALL('Projects Summary'),
        'Projects Summary'[Project]= __Project  && 'Projects Summary'[Expense Type]=__expenseType
    )
)
RETURN
__Criteria

But when I create the second one I get an error of circular dependency between the 2 columns but I don't understand why!!

score 0 · Answer 1 · answered Apr 03 '23 at 20:18

Try this:

Column = 
VAR vTable =
CALCULATETABLE (
    SUMMARIZE ( 'Projects Summary', 'Projects Summary'[Project], 'Projects Summary'[Expense Type] ),
    FILTER (
        'Projects Summary',
        CALCULATE (
            MAX ( 'Projects Summary'[Variance in $] ),
            ALLEXCEPT ( 'Projects Summary', 'Projects Summary'[Project] )
        ) < 85000
            && 'Projects Summary'[Variance in %] >= 0.05
    )
)
RETURN
    COUNTROWS(vTable) >= 1

Now just apply conditional formatting for the 'true' items.

Thank you so much Erick, how can I guarantee that it will only take a project if it has more than 5% in more than one expense type? because if it has above 5% in just one expense type it shouldn't be flagged — Rachel, Apr 03 '23 at 21:04

score 0 · Answer 2 · answered Apr 03 '23 at 22:22

0

Based on your example with the MNO project, I considered not only those who had more than one expense. To consider only those that contain more than one expense, use the measure:

Column = 
VAR vTable =
CALCULATETABLE (
    SUMMARIZE ( 'Projects Summary', 'Projects Summary'[Project], 'Projects Summary'[Expense Type] ),
    FILTER (
        'Projects Summary',
        CALCULATE (
            MAX ( 'Projects Summary'[Variance in $] ),
            ALLEXCEPT ( 'Projects Summary', 'Projects Summary'[Project] )
        ) < 85000
            && 'Projects Summary'[Variance in %] >= 0.05
            && CALCULATE (
                DISTINCTCOUNT ( 'Projects Summary'[Expense Type] ),
                ALLEXCEPT ( 'Projects Summary', 'Projects Summary'[Project] )
            ) > 1
    )
)
RETURN
    COUNTROWS(vTable) >= 1

answered Apr 03 '23 at 22:22

Erick Oliveira

24

thank you so much for helping me, so the condition is that if a project has 2 or more expense type with variance above 5% and those expense types have variance in money less than 85K then it should be true. – Rachel Apr 06 '23 at 15:41
That's right, Rachel. – Erick Oliveira Apr 07 '23 at 14:37
for some reason this column gives me always true even when there are rows where it should be false (bigger than 85K) , I am trying to figure out what I need to change to make it work (sorry for the delay in marking it as the solution) – Rachel Apr 11 '23 at 19:26

Complex PBI Dax Use Case

2 Answers2