Forum Discussion
Solved
Complex dynamic role filter
Hello to the forum, I have a question about a role filter in Power BI Desktop. Power BI is intended to filter the Orders table so that each person only sees the rows intended for them. Here i...
Hi DataInsights ,
I found that solution for my role in table Orders:
IF(
SUMX(
ADDCOLUMNS(
FILTER(Roles, Roles[Account] = USERPRINCIPALNAME()),
"Prüfung",
IF(
ISBLANK(Roles[Location]) && ISBLANK(Roles[Client]),
1,
IF(
Roles[Location] = Orders[Location]
&& ISBLANK(Roles[Client]),
1,
IF(
ISBLANK(Roles[Location]) &&
Roles[Client] = Orders[Client],
1,
IF(
Roles[Location] = Orders[Location]
&& Roles[Client] = Orders[Client],
1,
0
)
)
)
)
),
[Prüfung]
) > 0,
TRUE(),
FALSE()
)That work's very fine.
Thank you and have a nice day!
Greetings
Great solution! A simpler way to write it uses SWITCH instead of nested IFs, which scales well if you add more columns:
IF ( SUMX ( ADDCOLUMNS ( FILTER ( Roles, Roles[Account] = USERPRINCIPALNAME () ), "Prüfung", SWITCH ( TRUE, ISBLANK ( Roles[Location] ) && ISBLANK ( Roles[Client] ), 1, Roles[Location] = Orders[Location] && ISBLANK ( Roles[Client] ), 1, Roles[Client] = Orders[Client] && ISBLANK ( Roles[Location] ), 1, Roles[Location] = Orders[Location] && Roles[Client] = Orders[Client], 1, 0 ) ), [Prüfung] ) > 0, TRUE, FALSE )
Hi DataInsights ,
I found that solution for my role in table Orders:
IF(
SUMX(
ADDCOLUMNS(
FILTER(Roles, Roles[Account] = USERPRINCIPALNAME()),
"Prüfung",
IF(
ISBLANK(Roles[Location]) && ISBLANK(Roles[Client]),
1,
IF(
Roles[Location] = Orders[Location]
&& ISBLANK(Roles[Client]),
1,
IF(
ISBLANK(Roles[Location]) &&
Roles[Client] = Orders[Client],
1,
IF(
Roles[Location] = Orders[Location]
&& Roles[Client] = Orders[Client],
1,
0
)
)
)
)
),
[Prüfung]
) > 0,
TRUE(),
FALSE()
)
That work's very fine.
Thank you and have a nice day!
Greetings
Great solution! A simpler way to write it uses SWITCH instead of nested IFs, which scales well if you add more columns:
IF ( SUMX ( ADDCOLUMNS ( FILTER ( Roles, Roles[Account] = USERPRINCIPALNAME () ), "Prüfung", SWITCH ( TRUE, ISBLANK ( Roles[Location] ) && ISBLANK ( Roles[Client] ), 1, Roles[Location] = Orders[Location] && ISBLANK ( Roles[Client] ), 1, Roles[Client] = Orders[Client] && ISBLANK ( Roles[Location] ), 1, Roles[Location] = Orders[Location] && Roles[Client] = Orders[Client], 1, 0 ) ), [Prüfung] ) > 0, TRUE, FALSE )