Forum Discussion

AMBP1973

Helper III

10 months ago

Solved

Single Selection of aggregated columns

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 🙂

Ahmed-Elfeel

9 months ago

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.

8 Replies

xifeng_L
Super User
10 months ago
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~
Demo - Single Selection of aggregated columns.pbix20 KB
- AMBP1973
  Helper III
  10 months ago
  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
Anonymous
Not applicable
10 months ago
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
Anonymous
Not applicable
10 months ago
Hi AMBP1973,

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
- AMBP1973
  Helper III
  10 months ago
  Hello Anonymous , I did respond with further query below. I'd appreciate some further clarification. Thankyou

Ahmed-Elfeel

Super User

9 months ago

Hi AMBP1973,

I hope you are doing good today☺️❤️

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.

v-venuppu
Community Support
9 months ago
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.
Anonymous
Not applicable
9 months ago
Hi @AMBP1973,

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

Forum Discussion

Single Selection of aggregated columns

8 Replies

Related content

Power Query - Aggregate multiple columns within group by

Data Aggregation issue

Columns don't aggregate

Aggregate selected columns on chart

Variance column for Dynamically selected columns