How can I Filter using the output of a VAR in DAX

[TomEnns]

I have three data fields

• ProjectID 
• ActualHours 
• BudgetHours

I am trying to write a measure that will return the distinct count of ProjectID when SUM(BillableHours) - SUM(NonBillableHours) is greater than 0. 

I have tried putting the calculation of the difference into a measure o its own and tried to use that measure as a filter in another measure but it seems I cant use a measure as a filter. Or at least i cant get it to work. I also tried this: 

# Projects Over Budget = 
VAR HrsVar = (SUM(Phase[ActualHrs]) - SUM(Phase[BudgetHours]) > 0 )
RETURN
CALCULATE(DISTINCTCOUNT(Project[ProjectID]),HrsVar > 0)

 But I get a True/False expression error. 

Any ideas would be helpful. 

Thanks, 

[mwegener · Accepted solution]

Hi @TomEnns ,

try this. 

The SUMX function iterates over each unique project from the project table and then totals the projects over budget.

# Projects Over Budget =
SUMX (
    Project,
    IF (
        CALCULATE ( SUM ( Phase[ActualHours] ) - SUM ( Phase[BudgetHours] ) ) > 0,
        1,
        BLANK ()
    )
)

[TomEnns]

Hi, Thanks for the repsonse, t seems to do what I want however I am seeing disrepencies in my data now. The retunred value from the measure is "27" however as you can see from my image below i only have 20 projects where this seems to be true. The HrsVar column is a measure calulated as follows:  

HrsVar = (SUM(Phase[ActualHrs]) - SUM(Phase[BudgetHours])

Any thoughts on what might be causeing this? 

[mwegener]

Hi @TomEnns ,

please take a screenshot with additionally the measure "# Projects Over Budget" in the columns of the matrix to see which projects are counted.

[TomEnns]

Ok, you are on to something, it looks like for each phase that has an overage it also counts as theat project having an overage althgouht whn you sum the total for the project the overage is a negative as a total. IN the case below the total HrsVar for PPS-PH1 is actuall -44 however there are indivudual phases within that went over. I am hoping to only count the project when it is overbudget overall, ignoring if a few phases within went overbudget.  

[mwegener]

Hi @TomEnns ,

in total only the project should be counted, if the project is further divided on row level, the measure is calculated for each row with the additional filter.

Which 27 projects are counted?

[TomEnns]

Here is the list of the projects counted. The ones highlighted in red are expected to be counted, the ones not in red should not be counted. The ones not in red have phases inside of them are are overbudget but the overall total is not. It seems that if there are no phases overbudget then the measure works correctly as seen on the 5 projects at the bottom of the matrix. 

[mwegener]

Hi @TomEnns ,

What is the Key Column of the Project Table?

[TomEnns]

ProjectID

[mwegener]

Hi @TomEnns ,

can you share a screenshot of your model view?

[TomEnns]

yup, i am new to powerBI so its likly I've done something wrong here. 

[mwegener]

Can you check if the two measures really use the same columns for the calculation?

[TomEnns]

Well thats the ticket, I was using a differnt field for the first measure. Thats the issue with similarly label;ed fields i guess. I apologize for taking all your time on this. Ill mark your first post as the solution. Thanks so much for the help.