Only Function

[MIKIEMILLER]

Can the only function be used in measures and not dimensions?  Trying to get a dimension of only open(O) locations, yet when I enter in the set expression, the table comes back with invalid dimension.

Or is there a way in the load editor to combine the two tables into 1 that only shows open (O) units?

Table1:

Unit     Amount

1          10

2          5

3          7

4          9

5          10

Table2:

Unit     Open Status

1          O

2          C

3          O

4          O

5          C

Final Table Should look like

Unit     Amount

1          10

3          7

4          9

The formula I am using is:  =Only({<[Open Status]={'O'}>}[Unit Number])

Thanks,

[BalaBhaskar_Qlik · Accepted solution]

For dimensions, you need to also use: Aggr()

=Aggr(Only({<[Open Status]={'O'}>}[Unit Number]),[Unit Number])

[MIKIEMILLER]

Thank you Bala.  Didnt understand why Only Function never worked for me until now.