filter a column by another column's specific values

[Anonymous]

Hi,

Please see attached picture. I have this table containing working days (wd) used for solving change requests. The satus shows wether the request is finalized (final check approved) or is still not resolved (to be reviewed etc.). On this way we see how many working days that have been spent for different statuses. 

I want to make a measure that can filter the working days that are finalized and those that are still to be reviewed. I know I can do it by the filter pane, but I want to to it in a measure/dax so I can compare it in a stacked colum chart (see the second picture; its just something i made up and not based on correct numbers).

I am new to Power BI so really appreciate the help.

[Anonymous]

Hi, something like this?

WD Finalized = CALCULATE(SUM('Table'[WD]), KEEPFILTERS('Table'[Status]="Final check approved"))

WD Pending = SUM('Table'[WD]) - [WD Finalized]

Or you can create the 2nd one using a list of statuses to include like the first one but with many conditions in KEEPFILTERS. (Status IN { "Status 1", "Status 2", ...} )

Best Regards

Ulf

[az38]

Hi @Anonymous 

if I understand you correct ot will be enough to create a calculated table like

Filtered table = FILTER( 'Table',
'Table'[Status]="final check approved" || LEFT('Table'[Status], 14) = "To Be Reviewed"
)

[Anonymous]

Hi @az38 

Thanks for your reply. 

The screenshot I took is just a slice of a much larger table that is eventually going to be integrated from another datasource. I need a measure that filter out those who have "final check approved" and "final check rejected". I need another measure that filter out "to be reviewed (all of those kinds)". 

[az38]

@Anonymous 

sorry, do not understand. previous solution is not ok?

you can do something like above, like

Filtered table = FILTER( 'Table',
'Table'[Status]="final check approved" || 'Table'[Status]="final check rejected"
)

or

Filtered table = FILTER( 'Table',
LEFT('Table'[Status], 14) = "To Be Reviewed"
)

[Anonymous]

I actually want to calculate the average. Could you help me correct this one:

Measure 2 = CALCULATE(AVERAGE('Cr and leadtime'[WD]), 'Cr and leadtime'[Status]="final Check Approved" && "Final Check Rejected")

It works  if I dont use the && but I want to filter for both. Whats the right way to write the syntax?