Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jyothishree
Helper I
Helper I

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

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

Jyothishree_0-1713464893793.png

 

Thanks in advance

2 ACCEPTED SOLUTIONS
v-jianpeng-msft
Community Support
Community Support

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:

vjianpengmsft_0-1713492272266.png

table B:

vjianpengmsft_1-1713492304388.png

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:

vjianpengmsft_4-1713492816802.png

vjianpengmsft_5-1713492865313.png

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.

vjianpengmsft_6-1713492973093.png

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.

 

 

 

 

 

View solution in original post

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:

vjianpengmsft_0-1713749261332.png

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:

vjianpengmsft_1-1713749329841.png

 

 

 

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.

 

View solution in original post

4 REPLIES 4
Jyothishree
Helper I
Helper I

Thank you @v-jianpeng-msft 

v-jianpeng-msft
Community Support
Community Support

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:

vjianpengmsft_0-1713492272266.png

table B:

vjianpengmsft_1-1713492304388.png

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:

vjianpengmsft_4-1713492816802.png

vjianpengmsft_5-1713492865313.png

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.

vjianpengmsft_6-1713492973093.png

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.

 

 

 

 

 

@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?

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:

vjianpengmsft_0-1713749261332.png

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:

vjianpengmsft_1-1713749329841.png

 

 

 

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.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.