Filtering the dimension with condition

[alenb]

I have the following data, and I would like to display a simple table with all the Brands based on the Team to which a user belongs.

Brands:
LOAD * Inline [
Team, Brand
1, a
1, b
2, b
2, c
];

Users:
User, Team1, Team2
FRED, admin, user
WILMER, user

If I use the simple hardcoded example, it works. Only a, b Brands are shown in the table.

=if(Team=1, Brand)

But if I use a more complex condition, it is giving me "Invalid Dimension" error. Although condition by itself works OK in other expressions. 

=if(Len(only({<User={'FRED'}>}[Team1])) > 0, Brand)

Even the slightly modified version gives the same error. It looks like the problem might be in the "only" function, but I have no idea why or how to fix it.

=if(only({<User={'FRED'}>}[Team1]) = 'admin', Brand)

[agigliotti · Accepted solution]

Hi @alenb ,

You should use an Aggr function as below:

=Aggr( if( only( {< User = {'FRED'} >} [Team1] ) = 'admin', Brand ), Brand )

for the calculated dimension plus untoggle "include null values".

anyway in your case i can't see the relation between the two tables.

I hope it can help.

Best Regards

[Chanty4u]

can you try this

=if(aggr(only({<User={'FRED'}>}[Team1]) = 'admin', Brand),Brand)

[Chanty4u · Accepted solution]

or this one too

=if(aggr(Len(only({<User={'FRED'}>}[Team1])) > 0, Brand),Brand)

[alenb]

It doesn't give me error anymore, but looks like I've not set the correct condition. My intention was to only show the Brands from the teams of specific user. 

Basically WILMER should see only: b, c (only from Team2)

FRED should see: a, b, c (from Team1 and Team2)

I wanted to use the condition from the original question as the starting point but I see now this won't work, any idea how to achieve this?