Create three tables based on different condition in Power BI

Hi POSPOS - implement the Base Table and Transaction Table are connected via the User Name, DC, and Org columns where necessary.
Apply row-level security (RLS) on the Base Table using DAX expressions for filtering based on the logged-in user (USERNAME()).

Define RLS roles on the Base Table to restrict access based on the logged-in user ([User Name] = USERNAME()).

This table is filtered to include only the rows from the Transaction Table that match the Base Table data for the logged-in user.

Table1 =
FILTER(
'Transaction Table',
'Transaction Table'[User Name] = USERNAME() &&
LOOKUPVALUE('Base Table'[DC], 'Base Table'[DC], 'Transaction Table'[DC]) <> BLANK() &&
LOOKUPVALUE('Base Table'[Org], 'Base Table'[Org], 'Transaction Table'[Org]) <> BLANK()
)

This table identifies rows in the Transaction Table where the DC matches any DC from Table 1 but includes Org values that are not present in Table 1.

Table2 =
FILTER(
'Transaction Table',
'Transaction Table'[DC] IN DISTINCT('Table1'[DC]) &&
NOT('Transaction Table'[Org] IN DISTINCT('Table1'[Org]))
)

table identifies rows in the Transaction Table where the Org matches any Org from Table 1 but includes DC values that are not present in Table 1.

Table3 =
FILTER(
'Transaction Table',
'Transaction Table'[Org] IN DISTINCT('Table1'[Org]) &&
NOT('Transaction Table'[DC] IN DISTINCT('Table1'[DC]))
)

Hi POSPOS, Please try to create new tables with below code:

Table-1:
Table1 =
FILTER(
ADDCOLUMNS(
'Transaction Table',
"IsInBaseTable",
COUNTROWS(
FILTER(
'Base Table',
'Base Table'[User Name] = 'Transaction Table'[User Name] &&
'Base Table'[DC] = 'Transaction Table'[DC] &&
'Base Table'[Org] = 'Transaction Table'[Org]
)
)
),
[IsInBaseTable] > 0
)


Table-2:
Table2 =
FILTER(
ADDCOLUMNS(
'Transaction Table',
"IsAdditionalOrg",
NOT(
COUNTROWS(
FILTER(
'Table1',
'Table1'[DC] = 'Transaction Table'[DC] &&
'Table1'[Org] = 'Transaction Table'[Org]
)
)
)
),
[IsAdditionalOrg] > 0
)

table-3:

Table3 =
FILTER(
ADDCOLUMNS(
'Transaction Table',
"IsAdditionalDC",
NOT(
COUNTROWS(
FILTER(
'Table1',
'Table1'[Org] = 'Transaction Table'[Org] &&
'Table1'[DC] = 'Transaction Table'[DC]
)
)
)
),
[IsAdditionalDC] > 0
)

Hi POSPOS ,

Thanks for reaching out to the Microsoft fabric community forum.

To create three tables based on different condition in Power BI follow below step to meet your requirement.

Table 1: Create a table that includes data the user has access to from the Base Table.

For table1 use the below DAX Formula:

Table1 =

VAR CurrentUser = "UAA001" -- Replace with the current user dynamically

RETURN

FILTER(

    'Transaction Table',

    'Transaction Table'[User Name] = CurrentUser

Table 2: Create a table that includes additional “Org” values from the Transaction Table based on the “DC” values from Table 1.

For table2 use the below DAX Formula:

Table2 =

VAR Table1DCs = DISTINCT(SELECTCOLUMNS(FILTER('Transaction Table', 'Transaction Table'[User Name] = "UAA001"), "DC", 'Transaction Table'[DC]))

RETURN

FILTER(

    'Transaction Table',

    NOT 'Transaction Table'[Org] IN

        SELECTCOLUMNS(

            FILTER('Transaction Table', 'Transaction Table'[User Name] = "UAA001"),

            "Org", 'Transaction Table'[Org]

        ) &&

    'Transaction Table'[DC] IN Table1DCs

)

 

Table 3: Create a table that includes additional “DC” values from the Transaction Table based on the “Org” values from Table 1.

For table3 use the below DAX Formula:

 

Table3 =

VAR Table1Orgs = DISTINCT(SELECTCOLUMNS(FILTER('Transaction Table', 'Transaction Table'[User Name] = "UAA001"), "Org", 'Transaction Table'[Org]))

RETURN

FILTER(

    'Transaction Table',

    NOT 'Transaction Table'[DC] IN

        SELECTCOLUMNS(

            FILTER('Transaction Table', 'Transaction Table'[User Name] = "UAA001"),

            "DC", 'Transaction Table'[DC]

        ) &&

    'Transaction Table'[Org] IN Table1Orgs

)

I hope my suggestions give you good idea.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thankyou.