cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TomEnns
Regular Visitor

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

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, 

1 ACCEPTED SOLUTION
mwegener
Super User
Super User

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 ()
    )
)

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

12 REPLIES 12
mwegener
Super User
Super User

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 ()
    )
)

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

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])

2021-07-20 15_21_57-Project KPIs - Power BI Desktop.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Any thoughts on what might be causeing this? 

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.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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.  

 

2021-07-20 15_48_24-Project KPIs - Power BI Desktop.png

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?

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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. 

2021-07-20 16_05_20-Project KPIs - Power BI Desktop.png

Hi @TomEnns ,

 

What is the Key Column of the Project Table?

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


ProjectID

Hi @TomEnns ,

 

can you share a screenshot of your model view?

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

2021-07-20 16_22_37-Project KPIs - Power BI Desktop.png

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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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. 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!