Remove a certain order status from the data set

[PrashantRupani]

Hi,

I have a straight table in QS. 

Need to keep 'Shipped' Order status. In other words remove all statuses but keep only Shipped. There can be many order statuses like not shipped, order placed, shipped, delayed, delivered etc..so on

Order Date	Order City	Order Status	Sales	Revenue	Profit	Loss	Variance %	Profit %	Loss %
1/2/2021	NYC	Shipped	100	200	20	10	1%	5%	0%
1/3/2021	London	Shipped	200	300	15	10	2%	0%	10%
1/3/2021	Mumbai	Delivered	300	400	10	5	3%	1%	0%

I am writing this expn for Order status column : Aggr(Only( {< OrderStatus={'Shipped'}>} OrderStatus), Order Status)

But then I get is below. This is not what I want. I want this entire row to be removed. How can I achieve this?

Order Date	Order City	Order Status	Sales	Revenue	Profit	Loss	Variance %	Profit %	Loss %
1/2/2021	NYC	Shipped	100	200	20	10	1%	5%	0%
1/3/2021	London	Shipped	200	300	15	10	2%	0%	10%
1/3/2021	Mumbai	-	300	400	10	5	3%	1%	0%

[JuanGerardo]

Hi @PrashantRupani ,

Do not use Aggr() unless you find no other way. Try using set analysis in your expressions, like this:

Sales = Sum({<[Order Status] = {'Shipped'}>} Sales)

Revenue= Sum({<[Order Status] = {'Shipped'}>} Revenue)

Profit = Sum({<[Order Status] = {'Shipped'}>} Profit)

Loss = Sum({<[Order Status] = {'Shipped'}>} Loss)

Variance% = Column(3) / Column(2)

etc.

JG

[PrashantRupani]

Thanks . This solution striked me . But there are too many measures. I can do that but its consuming. 

Just trying to find a shorter way of doing it. Is there any other shorter way of doing it ? 

Would aggr function not work?

[JuanGerardo · Accepted solution]

OK, I see now what you want. Try to use the following expression for your "Order Status" dimension:

If([Order Status] = 'Shipped', [Order Status])

And uncheck the "Include null values" option for this column.

PD.- Aggr() uses large resources, so better if you use only when really needed.

JG

[PrashantRupani]

Thanks JG. It worked using Aggr ( I had to uncheck the "Include null values" option for this column.) and also the solution you gave. I am using the solution you provided since Aggr would use large resources. 

Thanks for the help on this. I appreciate you putting your time to solve my challenge.

PR