Trying to get a total with several requirements

[gboss87]

I am trying to get a total of Agreement Hours but need to filter several requirements.  Here is the SQL

sum(case when Time_Entry.Agr_Header_RecID is not null and AGR_Hours = Hours_Bill and Billable_Flag = 'True' and Time_Entry.Company_RecID <> 250 then AGR_Hours else 0 end) AGR_Hours_Total

and here is what I have tried to do in DAX

Actual_AGR_Hours_total = Calculate(Sum(Switch(not(ISBLANK('Time Table'[AGR_Header_RecID])) & 'Time Table'[AGR_Hours] = 'Time Table'[Hours_Bill] & 'Time Table'[Billable_Flag] = True & 'Time Table'[company_RecID] <> 250, 'Time Table'[AGR_Hours] , 0)))

The table names are different from the SQL and what I pulled into Power BI fyi.  'Time Table' is correct.

[v-yangliu-msft · Accepted solution]

Hi  @gboss87 ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Actual_AGR_Hours_total =
var _if=
  FILTER('Time Table',
    'Time Table'[Agr_Header_RecID]<>BLANK() &&
    'Time Table'[AGR_Hours]='Time Table'[Hours_Bill]&&
    'Time Table'[Billable_Flag]="True"&&
    'Time Table'[Company_RecID]<>250)
return
SUMX(_if,[AGR_Hours])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lukiz84]

Why do you use SWITCH?

Actual_AGR_Hours_total = 
   CALCULATE(
      SUM(
         'Time Table'[AGR_Hours],
         FILTER(
            'Time Table',
            NOT(ISBLANK('Time Table'[Agr_Header_RecID])),
            'Time Table'[AGR_Hours] = 'Time Table'[Hours_Bill],
            'Time Table'[Billable_Flag] = TRUE,
            'Time Table'[company_RecID] <> 250
         )
      )
    )