topic Re: filter a column by another column's specific values in DAX Commands and Tips

filter a column by another column's specific values

Anonymous — Thu, 13 Feb 2020 15:19:27 GMT

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.

Re: filter a column by another column's specific values

az38 — Sun, 16 Feb 2020 19:46:36 GMT

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

Re: filter a column by another column's specific values

Anonymous — Mon, 17 Feb 2020 09:30:08 GMT

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)".

Re: filter a column by another column's specific values

az38 — Mon, 17 Feb 2020 09:40:24 GMT

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

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

Re: filter a column by another column's specific values

Anonymous — Mon, 17 Feb 2020 09:45:46 GMT

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?

Re: filter a column by another column's specific values

Anonymous — Mon, 17 Feb 2020 09:54:59 GMT

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