CODEWITHSUNDEEP
powerbidaxpowerbi-desktopdaxstudio
Rachel
Rachel

Reputation: 218

Complex PBI 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 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

enter image description here

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!!

Upvotes: 0

Views: 157

Answers (2)

Erick Oliveira
Erick Oliveira

Reputation: 64

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

Upvotes: 0

Erick Oliveira
Erick Oliveira

Reputation: 64

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.

Upvotes: 0

Related Questions