Create a chart with data from two different tables using the same X axis and a filtering system

[Anonymous]

Hi there everyone!
Today I bring a little more complex question, I hope someone has already dealt with this problem!

My schema is the following:

Two main tables ( v_detalles_asignacion and v_headcounts) , each one come from a view made in SQL. This tables have in comon the following variables:

• SemanaAlta (WeekOfIngress) which is a text with the data inside it like this example:
  "35-2022"
  This represents the Week 35 and year 2022 

• Categoria ( Category ) which is a text with the same categorical values in both of the tables. For example: 
  "Manager"

I have created a filter which depends on SemanaAlta so I can filter v_headcounts and v_detalles_asignacion both at the same time. Actually I use v_headcounts  to filter v_detalles_asignacion: 

At the time of displaying the results in a Bar char in where the X axis is going to be Categoria ( of v_headcounts ) . PowerBi doesn't detect that there are the same variable, altough the both tabels are linked thru SemanaAlta. Example:

Total recursos = Count Distinct of variable "names" of v_headcounts.

Total asignados = Count Distinct of variable "names" of v_detalles_asignacion.

Here in this picture when I set the X axis to be Categoria of v_headcounts I got the correct value for Total recursos , but incorrect for Total asignados.

Instead of appearing 6 in Total asignados ( Count distinct of variable "names" of v_detalle_asginacion), it should be divided along the categorical values of Categoria ( X axis ) [ Senior Consultant, Manager and Senior Manager ], getting the following result: 

3: Senior Consultant,  2: Manager and  1:Senior Manager. Like when I put in the X axis the Categoria of v_detalles_asignacion.

What I'm pursuing here is combine data in a chart from two different tables which have the same X axis ( Categoria ), those tables are related by a variable which will use a table to filter the other. That variable which filters the tables will be placed in a PowerBi Segment Filter. 

The problem happens because PowerBi doesn't let me create an active relationship between Categorias, due to I have already created a relationship between SemanaAlta  to create the filtering system:

My approach and first solution is divide my chart in two like the following: 

Here I can place in each X axis of each chart the field Categoria which divides in the correct buckets each value and also makes the filtering with SemanaAlta. 

But I really need to get the following chart working, and : 

In where I combine both data[Distinct Count of variable name of both views(tables)] into one single chart with just one common X axis ( Categoria ).

Thank you very much! ( I know I'm short on the data, so if you need more data or a better or deeper explanation just ask!)

[MFelix]

Hi @Anonymous ,

In this case the best option is to create a dimension table for the categories, and make the relationship between both tables to go trough the dimension table, also be aware that since you have the active relationship of the semanaalta you also need to have a dimension table for that column this will create a model that will have a relationship of 

Headcounts*- 1 SemanaAlta 1 -*  Assignacion

Headcounts*- 1 Categorias 1 -*  Assignacion

Altough you only have one value on the Headcount be sure to make it has the many side of the relationships then you should use the Categorias and the SemanaAlta in your visualizations and you should get expected result.