Single Selection of aggregated columns

[AMBP1973]

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]

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.

[v-pgoloju]

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]

Hello @v-pgoloju , I did respond with further query below. I'd appreciate some further clarification. Thankyou

[v-pgoloju]

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

[xifeng_L]

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~

[AMBP1973]

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