Filter in straight table

[Trick17]

hi all, 

I'm looking to create the following straight table in the most efficient way. 

I have a large data table with different information like No, Dep and Rev. 

I want to have the No. in the first column of a straight table but only those that are from Dep. FRG. 

So ultimately i want the following result: 

I manage to get the results with the IF funktion, however the table loads rather slowly.  I can also get there by creating a filter window, however, I'm wondering is there a way to do this in set analysis, or does it only work on numeric values?

I hope someone can help me out here!

[anthonyj]

Hi,

I assume that Rev. is a dimension and not an aggregated measure. If that is the case you can use the only() function to insert set analysis for your required Dep. You can add the Dep as a measure like this:

ONLY({$<Dep={FRG}>}Dep)

But if you want to add it as a dimension you can use the aggregation

=AGGR(ONLY({$<Dep={FRG}>}Dep), No, Rev)

If Rev is a measure you could add the set analysis in there with:

=Sum({$<Dep={FRG}>}Rev)

I hope this helps.

Thanks

Anthony