Swapping sets of columns in a table based on role type

[Jays_D]

Hi Everyone,

I have a parameter in Power BI which is for a Role type. Based on the role you select, it replaces the role column in a table with the relevant role selected.

What I am trying to do is, based on the role selected, it will also insert a number of other relevant columns into the table.

Example:
If Role 1 is selected, also insert columns:

Role 1 Start Date 

Role 1 Rework Date

Role 1 End Date

If Role 2 is selected:

Role 2 Start Date

Role 2 Rework Date

etc

Admittedly, I am very new to Power BI, but have searched for this scenario on the internet and cannot find a solution.

[amitchandak]

@Jays_D , Not very clear, but these approaches can help

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[Jays_D]

Greatly appreciate your response @amitchandak . Let me try to explain, the articles above don't cover what I am hoping to achieve.

I have the following columns:

Role 1 Name

Role 1 Start Date

Role 1 Rework date

Role 1 End Date

Role 2 Name

Role 2 Start Date

Role 2 Rework date

Role 2 End Date

Role 3 Name

Role 3 Start Date

Role 3 Rework date

Role 3 End Date

I have a Parameter already setup called "Roles" which consists of:

Role 1 Name

Role 2 Name

Role 3 Name

This Parameter I have as a column in my table so whenever I select (for example) Role 1, the "Role" column in my table only shows Role 1 Name.

What I want to do is:

When I select "Role 1" I also want it to show in the table:

Role 1 Start Date

Role 1 Rework date

Role 1 End Date

[v-jianboli-msft]

Hi @Jays_D ,

Is your data Role1 xxxx one column of data or two columns of data?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Jays_D]

Thanks for the response @v-jianboli-msft .

Each is it's own column. Example

Role 1 Name

Role 1 Start Date

Role 1 Rework Date

Role 1 End Date

Role 2 Name

Role 2 Start Date

Role 2 Rework  Date

Role 2  End Date

Role 3 Name

etc

How I envision this working, is a parameter which has Role 1, Role 2, Role 3 etc

If I select Role 1 in the Parameter it adds to my table:

Role 1 Name

Role 1 Start Date

Role 1 Rework Date

Role 1 End Date

Static Column

Static Column

Static Column

Static Column

Static Column

Static Column

Then, perhaps I select Role 2 in my Parameter which then swaps out the relevant columns in my table:

Role 2 Name

Role 2 Start Date

Role 2 Rework Date

Role 2 End Date

Static Column

Static Column

Static Column

Static Column

Static Column

Static Column

I'd like to avoid sample data sets if I can as I am very new to Power BI and work in a Financial Institution, I am just not at a comfortable level - I do apologies.

Hopefully the above will paint a clearer picture? Thank you so much again for taking the time to respond.

[v-jianboli-msft · Accepted solution]

Hi @Jays_D ,

Based on your description, I have created a simple sample:

Please try:

First create a field parameter:

Then add a new column to the parameter table:

Then use the 'Role' column to create a slicer:

Final output:

Best Regards,

Jianbo Li

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

[Jays_D]

Thank you @v-jianboli-msft !
This is precisely what I was looking for ! Truly appreciate this, has solved a majot bug bear for me !

Apologies for the late response.