Custom column in New Table by looking up the values from existing Table

[Jyothishree]

Hi , I  have some roles in ‘Table A’ with ID’s. Now each ID must have all the roles assigned but some Id’s are not assigned with certain roles, which means there are some missing roles for each ID. We have ID and Roles in one Table and ‘ID’ with other details in another Table. Relationship is created these both Tables and columns from both tables are implemented on visuals. Requirement is a table which displays the ID column, Roles column and Role Assigned to ID/Role NOT Assigned to ID

So to get this working, I have applied a measure

Assigned Status =

  VAR Selected_Role = SELECTEDVALUE('Table A'[Roles])

  VAR Selected_ID = SELECTEDVALUE(‘Table B’ [id])

RETURN

IF(Selected_Role = Selected_ID, "Not Assigned", "Assigned")

Above measure gives us the details of ‘Assigned’ or ‘Not Assigned’ status for each ID but We also have a slicer of Email with search option when a user selects a option from the ‘email’ slicer, all the ID’s are retrieved  where only one row with assigned and all other rows as unassigned but we want ‘Assigned Status’ measure column must show up only ‘Assigned’ rows when user selects options from ‘Email’ slicer but show up all the values (as above measure) when other slicers are selected.

As the measure limits the implementations, Is there any way to create a new Table with two columns with all  ‘ID’ and  all ’Roles’( each id should have all the roles). Where we can get the assigned and Unassigned status as a third column? Just for information we have 60 ID’s and 45 roles, so number of rows for each ID must be 45. If the Assigned status is in a column instead of measure column it would be useful in further implementations.

Just for example : If 1,2,3 are Id’s and A, B, C,D, E are roles then The New table must look link

Thanks in advance

[v-jianpeng-msft · Accepted solution]

Hi, @Jyothishree 

According to your description, you want a table, each ID in this table corresponds to a role.60 IDs and 45 roles, you need to generate a table with 60*45 rows according to your meaning. I used the following sample data:

table A:

table B:

I use the following DAX expression to generate a calculated table where each ID corresponds to a role:

Result table =
VAR _roles =
    SUMMARIZE ( 'Table A', 'Table A'[Role] )
VAR _ID =
    SUMMARIZE ( 'Table B', 'Table B'[ID] )
RETURN
    CROSSJOIN ( _ID, _roles )

Here are the results:

This way, each ID will correspond to a role. If you want to add a third column to this table, you just need to create a new calculated column in this table according to your calculation logic.

I've provided the PBIX used this time below . If you are not familiar with CROSSJOIN and SUMMARIZE function, you can click on the link below to learn how to use the summary

CROSSJOIN function (DAX) - DAX | Microsoft Learn

SUMMARIZE function (DAX) - DAX | Microsoft Learn

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Jyothishree]

Thank you @v-jianpeng-msft 

[v-jianpeng-msft · Accepted solution]

Hi, @Jyothishree 

According to your description, you want a table, each ID in this table corresponds to a role.60 IDs and 45 roles, you need to generate a table with 60*45 rows according to your meaning. I used the following sample data:

table A:

table B:

I use the following DAX expression to generate a calculated table where each ID corresponds to a role:

Result table =
VAR _roles =
    SUMMARIZE ( 'Table A', 'Table A'[Role] )
VAR _ID =
    SUMMARIZE ( 'Table B', 'Table B'[ID] )
RETURN
    CROSSJOIN ( _ID, _roles )

Here are the results:

This way, each ID will correspond to a role. If you want to add a third column to this table, you just need to create a new calculated column in this table according to your calculation logic.

I've provided the PBIX used this time below . If you are not familiar with CROSSJOIN and SUMMARIZE function, you can click on the link below to learn how to use the summary

CROSSJOIN function (DAX) - DAX | Microsoft Learn

SUMMARIZE function (DAX) - DAX | Microsoft Learn

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Jyothishree]

@v-jianpeng-msft  Thank you very much for investing time to investigate it. As mentioned above, the third column must say if it ‘assigned role’ or ‘unassigned’ role to that particular ID refering at the ‘Table A’. As Table ‘A’ has ID column and Roles column but all the roles are not assigned to all the ID’s. So now looking at the values in Table A, in the Result Table we need ‘Assigned’ or ‘Not Assigned’ as Third column. Which means if each role is assigned to all Id’s then the entry for that role and Id (row) must be “Assigned” else “Not Assigned”. I think calculated column will not support to compare values from other table?

[v-jianpeng-msft · Accepted solution]

Hi, @Jyothishree 

Thank you very much for your reply. You can use the following DAX expression to get the status of whether the ID is assigned in Table A, and then use the IF function to determine whether the current ID is assigned:

table A:

DAX:

Column = 
VAR _status = CALCULATE(MAX('Table A'[Column]),FILTER('Table A','Table A'[ID]='Result table'[ID]))
RETURN IF(_status="assign","assign","unassign")

Here are the results:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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