Dynamic Table - Not Affected by Source Table fields except one Field

[Anonymous]

Hi All,

I am new to Power BI. Please assist, urgent help needed.

 As you can see in below Gantt chart, I am trying to display resource allocation with respect to Skills (present in vertical redacted part) along with training timelines. (I have grouped the resources assigned for the skills)

When I select any skill from the chart by clicking on bar, another Gantt chart appear below the first Gantt chart(refer to 2nd screenshot) and 2nd Gantt chart show the bars for each resource who need to learn the selected skill from first Gantt chart.

And I am getting all the resources in 2nd Gantt chart for the selected skill when the training timelines are same for allocated resources.

But when training timelines are different for resources assigned to learn a skill, this method is not working. Let’s take an example, 2 resources assigned to learn Java but have different timelines, so when I select the bar of JAVA skill, 2nd Gantt chat show only one resource (refer to 2nd screenshot).

The reason why it’s not working is because both the Gantt charts are using same “Skill Gaps” table.

So, I want that 2nd Gantt Chart won’t be affected by any field coming from “Skill Gaps” Table except Skill Field.

 In order to make it work I am trying to create a disconnected table for 2nd Gantt Chart which derives from “Skill Gaps” Table and filter it based on selected skill.

But this Dax expression is not working and giving no output.  Please help to figure it out how to create a New Table which won’t affected by fields coming from “Skill Gaps” Table except Skill Field.   

Here is the attached .pbix file:

https://drive.google.com/file/d/1U-wwgGG11ViRlJnuAq6v_9TThuLxgVGK/view?usp=sharing

[Anonymous]

Hi @Anonymous 

I am so glad to hear that you have solved your issue. Please consider Accept it as the solution to help the other members find it more quickly.

Best Regard

Community Support Team _ Ailsa Tao

[Anonymous · Accepted solution]

I am able to solve the issue. I have created a disconnected table and used treatas function to make the visual relationship between tables. I have used following expression in measure filed in disconnected table

[bcdobbs]

Hi @Anonymous,

Your DAX is creating a calculated table. However caclulated tables are only refreshed when the model is loaded with data. Eg they are never dynamic. The reason it therefore shows blank is there is no SELECTEDVALUE so that line returns blank.

You could create a dynamic table in a variable within a measure but I suspect there might be easier ways of achieving what you are trying. Are you able to share a demo pbix file?

Thanks

Ben

[Anonymous]

@bcdobbs  Thanks for responding. Sure I will share the pbix file but I am not getting option to attach the  .pbix file. Could you share your email, I can sent you...

[bcdobbs]

I created two new tables:

Skills = 
    DISTINCT ( 'Skill Gaps'[Skills] )

SkillsTable2 = 
SUMMARIZE(
    'Skill Gaps',
    'Skill Gaps'[Skills],
    'Skill Gaps'[Resource],
    'Skill Gaps'[Start_Date],
    'Skill Gaps'[End_Date]
)

Swapped both your visuals to use Skills from the new Skills dimension table.

In the second visual switched the other fields to come from SkillsTable2

[Anonymous]

Thanks  @bcdobbs for looking into it but the problem still persists. Sorry in case I was not clear before about the problem.

In my data, for skill Java, there are two resources assigned (James & William) but both has different timelines to learn Java.

But in below screenshot, I selected James’s bar for Java Skill from Ist Gantt Chart, but 2nd Gantt Chart shows only James. I want to see both James & William in 2nd Gantt chart irrespective of where I click on Java bar.

My expectation is to see the 2nd Gantt chart output as below