cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Filter Multiple Columns from single table

I have a table RT Hr DB an it has multiple columns. Want to filter column Department=UW and Hour Type=Overtime and sum the Days. This is what I have so far

SUMX(FILTER(FILTER('RT Hr DB','RT Hr DB'[Hour Type]="Vacation"), RT Hr DB'[Department]="Underwriting"), 'RT Hr DB[Days])
6 REPLIES 6
Super User

Hello @Anonymous

Give this a try.

```UW OT =
CALCULATE(
SUM ( 'RT Hr DB'[Days] ),
'RT Hr DB'[Department] = "Underwriting",
'RT Hr DB'[Hour Type] = "Overtime")```
Anonymous
Not applicable

I tried this but it still does not give me the right answer.

Super User

Hello @Anonymous

Please provide some sample data and and example of the expected outcome.

Anonymous
Not applicable
Formula 1 = (SUMX(filter('RT Prod HC',[Team]="UW"),[Workdays])-(CALCULATE(SUM('RT Hr DB'[Days]),'RT Hr DB'[Hour Type] = "Vacation",'RT Hr DB'[Department] = "Underwriting"))-CALCULATE(SUM('RT Hr DB'[Days]),'RT Hr DB'[Hour Type] = "Partial Hours Off",'RT Hr DB'[Department] = "Underwriting")+(CALCULATE(SUM('RT Hr DB'[Days]),'RT Hr DB'[Hour Type] = "Overtime",'RT Hr DB'[Department] = "Underwriting"))/(SUMX(FILTER('RT Prod HC',[Team]="UW"),'RT Prod HC'[Prod HC])))

Formula 2 =
(SUMX(filter('RT Prod HC',[Team]="UW"),[Workdays])-((SUM('UW Vacation'[Days]))/(SUMX(FILTER('RT Prod HC',[Team]="UW"),'RT Prod HC'[Prod HC])))+((SUM('UW Overtime1'[Days]))/(SUMX(FILTER('RT Prod HC',[Team]="UW"),'RT Prod HC'[Prod HC]))))

Formula 1 is when I tried to filter the same table multiple times. Formula 2 is when I duplicate the table in power query and then put in the filters. Both have the same data but they are giving me 2 different answers.
Super User

@Anonymous

In your picture, what number is wrong and what should it be?  Also, we will need sample data from the tables involved and to know how the model relationships are set up.  If you can share your .pbix file that will help us answer your question.

Anonymous
Not applicable

This table is where the raw data is taken from. I duplicated this table to filter out without a formula. The line graph with value 8.95 should be the right one which i got by duplicating the table and filtering it separately rather than doing multiple filters with the formula.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.