DAX syntax to flag user based on its existence in bridge table

[Prashh]

Hello Experts,

I am implementing Security in SSAS Tabular Model. In it, I am running into scenario, where I want a user to be "flagged" dynamically based on its existence in a bridge table.

Below is the scenario:

1. I have UserDimension table:
UserAlias
Domain\User1
Domain\User2
Domain\User3

2. I have Bridge table:
UserAlias FinGeoKey
Domain\User1 11795
Domain\User1 19033
Domain\User1 31333

3. With this, I want to create DAX expression, which should dynamically flag record as 0 or 1, based on its existence in Bridge table.


Thus, I should get flag of 1 for User1 and
flag of 0 for User2 & User3.


I tried below expression, but no luck:

Flag:=IF( CALCULATE( COUNTROWS( BridgeTable ), FILTER( BridgeTable, BridgeTable [UserAlias]=USERNAME()) ) > 0, 1, 0)


Any ideas on how to proceed? Please let me know if I missed anything.

Thanks,
Prasad

 

[Ozeroth]

Hi there,
I'm assuming you have a relationship between BridgeTable[UserAlias] and UserDimension[UserAlias].

Either of these should work as a calculated column in UserDimension (or otherwise iterating over rows of UserDimension):

= IF ( COUNTROWS ( RELATEDTABLE ( BridgeTable ) ) > 0, 1, 0 )

= IF ( CALCULATE ( COUNTROWS ( BridgeTable ) ) > 0, 1, 0 )

Was there a reason you had the USERNAME() function in the expression you were trying? From your description it didn't sound like this 0/1 flag needed to take account of current user's credentials.

 

[Prashh]

Hi Ozeroth,

Thanks for your reply.
I need to use this flag for implementing tabular security.

Flags are not hard coded in spreadsheet; instead that flag value should "dynamically" come, when user opens the cube.
Ex: When User1 opens workbook, he should see flag value as 1; while when User2 opens workbook, he should see flag value of 0.

Thanks

 

[Ozeroth]

Ah I see, the flag should be 1 if the current user exists in BridgeTable, otherwise zero.

I would use CONTAINS as follows:

=IF ( CONTAINS ( ALL ( BridgeTable[UserAlias] ), BridgeTable[UserAlias], USERNAME () ), 1, 0 )

 

[Prashh]

Hi Ozeroth,
Thanks for the code. Its working as expected.

Thanks,
Prasad