Create three tables based on different condition in Power BI

[POSPOS]

Hi All,

I have two tables, Base table and Transaction Table. Base table has a row level security based on user name.

I have to create three new tables based on these tables usig DAX:

Base Table :

User Name	DC	Org
UAA001	1120	5100
UAA001	1121	5100
UAA001	1122	51001
UAA001	1123	51001
UAA001	1124	51001
UAA001	1125	51001
UAA002	1120	6100
UAA002	1121	6100
UAA002	1128	51002
UAA002	1129	51002
UAA002	1130	51003
UAA002	1131	61003
UAA003	1141	5100
UAA003	1142	51001
UAA003	1143	51001

Transaction Table:

User Name	DC	Org	Amount
UAA001	1120	5100	100
UAA001	1121	5100	101
UAA001	1122	51001	102
UAA001	1123	51001	103
UAA001	1124	51001	104
UAA001	1125	51001	105
UAA002	1120	6100	106
UAA002	1121	6100	107
UAA002	1128	51002	108
UAA002	1129	51002	109
UAA002	1130	51003	110
UAA002	1131	61003	111
UAA003	1141	5100	112
UAA003	1142	51001	113
UAA003	1143	51001	114
UAA004	1120	4100	115
UAA005	1140	5100	116
UAA006	1140	5100	117

New table : Table 1

1. Based on the user , create a table based on the data they have access to from the base table. Eg: for UAA001, Table 1 will be

User Name	DC	Org	Amount
UAA001	1120	5100	100
UAA001	1121	5100	101
UAA001	1122	51001	102
UAA001	1123	51001	103
UAA001	1124	51001	104
UAA001	1125	51001	105

New table : Table 2

2. Based on the DC from Table 1, look for any additional "Org" in the transcation table that are not in Table 1. Here, we should not restrict to just the user who has logged in, will have to look at the entire table irrespective of the user.

eg: Here, 1120,1121,1122,1123,1124,1125 are the "DC" from Table 1, we look for the same "DC" in the transaction table for all the users and identify any additional "org" thats not in Table 1 . Org=6100,61003,4100 are not in Table 1.They should come in Table 2.

Below are the additional "Org" not in Table 1.

User Name	DC	Org	Amount
UAA002	1120	6100	106
UAA002	1121	6100	107
UAA004	1120	4100	115

Tabl 2: 

User Name	DC	Org	Amount
UAA001	1120	6100	213
UAA001	1120	4100	115

New table : Table 3

3. Based on the "Org" from Table 1, look for any additional "DC" in the transcation table that are not in Table 1. Here, we should not restrict to just the user who has logged in, will have to look at the entire table irrespective of the user.

eg: Here, 5100, 51001 are the "Org" from Table 1, we look for the same "Org" in the transaction table for all the users 

and identify any additional "dc" thats not in Table 1 . DC=1141,1142,1143,1140 are not in Table 1.They should come in Table 3.

Below are the additional "Org" not in Table 3.

User Name	DC	Org
UAA001	1141	5100	112
UAA001	1142	51001	113
UAA001	1143	51001	114
UAA001	1140	5100	116
UAA001	1140	5100	117

then, Table 3 is : 

User Name	DC	Org
UAA001	1141	5100	112
UAA001	1142	51001	113
UAA001	1143	51001	114
UAA001	1140	5100	223

Can some one please advise on how this can be achieve. any help will be much appreciated.

[rajendraongole1 · Accepted solution]

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]))
)

[anmolmalviya05 · Accepted solution]

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
)

[v-tsaipranay · Accepted solution]

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.

[anmolmalviya05 · Accepted solution]

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
)

[rajendraongole1 · Accepted solution]

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]))
)