I want to ask if its possible to use something like IN operator in Power BI RLS DAX. The approach I am using now seems very convoluted to me, and I want to ask if it could be done better and simpler.
I have two types of identifiers in my Fact table: Code and GroupCode. Code is NOT NULL identifier and can have multiple unique GroupCodes and GroupCode can be NULL and can be assigned to multiple Code. Example Fact table:
| Code | GroupCode | Client |
|---|---|---|
| 1 | a | John |
| 1 | b | Susie |
| 1 | c | Mark |
| 2 | a | John |
| 2 | NULL | Mary |
| 3 | b | Susie |
I want to create report where User enters by Code, but see all rows with it GroupCode. In SQL it would be:
SELECT * FROM table
WHERE GroupCode IN (SELECT GroupCode FROM TABLE WHERE Code = '2') or Code = '2'
For now I created Dictionary table from my Fact table with Code and CodeDict, where CodeDict = ISNULL(GroupCode, Code). Also added CodeDict column to Fact table to create relation based on it, between Dict and Fact tables. Should be (1:*), but it shows (*:*).
Example Dict table:
| Code | GroupCode |
|---|---|
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 2 | 2 |
| 3 | b |
As for RLS rule it is set on Dict table as [Code] = @USERPRINCIPALNAME().
So for example if User open report using Code = 2. RLS will filter Dict table so CodeDict will be (a, 2) and by them, it will filter Fact table to shows rows with Code=(1, 2)
It's very convoluted approach and I don't like it, but I have no idea I could make it other way. I worry about (*:*) relation and that I have to create Bridge Dict table.
What do you think about this approach to this problem?
Is there a way to implement IN operator in RLS rule?
