topic Re: Single Selection of aggregated columns in DAX Commands and Tips

Single Selection of aggregated columns

AMBP1973 — Fri, 31 Oct 2025 03:13:58 GMT

Hello,

I am seeking a solution for the following:

I have created the following Dax to aggregate 3 columns into one to aggregate 'positions assigned against roles for a particular task:

Content Manage. Roles Aggr = 'DI Review'[Content Author] & "-" & 'DI Review'[Content Manager] & "-" & 'DI Review'[Approver]

Context

Essentially, all three columns have the same attributes (positions) against a particular role (Content Author, Content Manager, Approver); in this case the 'role' is the column title i.e.

Each row in the dataset is repesented as a task, and against that task are three 'roles' required to complete the task. For any particular task a 'position' can be assigned 1 or all of those roles for that individual task. Example of data from spreadsheet below:

Solution Required:

I would like to show how many tasks by position, but have the role/s defined in the chart also, and if possible ensure the 'total' numbers are a distinct number against the tasks and not a reflection of how many roles a 'position' has across all tasks.

Here is a chart I started with

I would also like a slicer that enables selection of a single position to filter by. At the moment it only gives me the below

Hopefully this makes sense. Unfortunately, I cannot share the data.

Any assistance would be much appreciated 🙂

Re: Single Selection of aggregated columns

xifeng_L — Fri, 31 Oct 2025 07:10:47 GMT

Hi AMBP1973

You can use PowerQuery to convert the table structure into the structure shown in the following figure.

Then, create the measure and bar chart as below:

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Single Selection of aggregated columns

Anonymous — Tue, 04 Nov 2025 02:51:24 GMT

Hi AMBP1973 ,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to xifeng_L for prompt and helpful response.

Just following up to see if the Response provided by community member were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

Re: Single Selection of aggregated columns

AMBP1973 — Thu, 06 Nov 2025 01:38:26 GMT

Hello xifeng_L , Thankyou so much for your solution. Unfortunately I cannot open your demo file as my organisation does not allow this file, could you please provide the dax and simple steps to follow. Many thanks

Re: Single Selection of aggregated columns

Anonymous — Mon, 10 Nov 2025 03:44:37 GMT

Hi AMBP1973,

Best regards,
Prasanna Kumar

Re: Single Selection of aggregated columns

AMBP1973 — Mon, 10 Nov 2025 23:56:38 GMT

Hello Anonymous , I did respond with further query below. I'd appreciate some further clarification. Thankyou

Re: Single Selection of aggregated columns

Ahmed-Elfeel — Fri, 14 Nov 2025 00:41:03 GMT

Hi AMBP1973,

I hope you are doing good today☺️❤️

It is hard to answer without Sample data but i will do my best to solve your issue...So The issue is that your current approach with concatenation makes it difficult to analyze individual positions and roles separately

Instead of concatenating, create separate tables for analysis:

Create a Roles Table:

Roles = VAR RoleTable = UNION( SELECTCOLUMNS('DI Review', "Position", 'DI Review'[Content Author], "Role", "Content Author", "Task", 'DI Review'[Task ID]), SELECTCOLUMNS('DI Review', "Position", 'DI Review'[Content Manager], "Role", "Content Manager", "Task", 'DI Review'[Task ID]), SELECTCOLUMNS('DI Review', "Position", 'DI Review'[Approver], "Role", "Approver", "Task", 'DI Review'[Task ID]) ) RETURN RoleTable

Then Create Measures for Analysis :

Total Distinct Tasks by Position:

Total Tasks = CALCULATE( DISTINCTCOUNT('Roles'[Task]), ALL('Roles'[Role]) )

Tasks by Role (for breakdown😞

Tasks by Role = DISTINCTCOUNT('Roles'[Task])

Role specific measures:

Content Author Tasks = CALCULATE( DISTINCTCOUNT('Roles'[Task]), 'Roles'[Role] = "Content Author" ) Content Manager Tasks = CALCULATE( DISTINCTCOUNT('Roles'[Task]), 'Roles'[Role] = "Content Manager" ) Approver Tasks = CALCULATE( DISTINCTCOUNT('Roles'[Task]), 'Roles'[Role] = "Approver" )

Then Create Your Visualize you prefer (I will Skip this Step cause its easy)

But, If you prefer a matrix view Create this:

Task Count by Role = VAR SelectedRole = SELECTEDVALUE('Role List'[Role]) RETURN IF( ISBLANK(SelectedRole), [Total Tasks], CALCULATE( DISTINCTCOUNT('Roles'[Task]), 'Roles'[Role] = SelectedRole ) )

Final Step is Creating Supporting Tables:

Role List = DATATABLE( "Role", STRING, { {"Content Author"}, {"Content Manager"}, {"Approver"} } )

Tell me if you need further help ☺️❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Single Selection of aggregated columns

v-venuppu — Tue, 18 Nov 2025 12:07:54 GMT

Hi AMBP1973 ,

Thank you Ahmed-Elfeel for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

Re: Single Selection of aggregated columns

Anonymous — Tue, 02 Dec 2025 02:42:24 GMT

Hi @AMBP1973,

Best regards,
Prasanna Kumar