Data Model - Data does not follow the hierarchy

[sydney20]

Hi! Would appreciate insights on where my model is going wrong.

I have these tables:

(1) Base headcount of employees w/c I am planning to use as dimension

(2) Data that would be used for the measures - this table has a person identifier that could be linked to table 1

(3) Dimension table for Region, Business Unit and Country

Basically, the stakeholder would want a visual and table that shows how many employees meets the metrics and how many are not. So the table 1 have a mapping of region and business unit per employee w/c was then connected to table 3.

Now, the issue is the matrix table. Since they would want as well the list of workers that does not have data in table 2, I have added +0 at the latter part of the DAX that simply count the rows, sum some data, etc. What happens is that, if I visualize it as a table: Region - Business Unit - Worker, the worker appears to ALL regions and BUs w/c should not be the case.

Any insights on where did I went wrong? Any help would be appreciated 

@amitchandak @lbendlin 

[sydney20]

[v-janeyg-msft]

Hi, @sydney20 

Your model is not a star structure, the dimension table is also connected to the dimension table, which may cause problems. And I don't know which table columns you put in the matrix table, and what's the dax formula like. Can you explain more datails?

Best Regards,
Community Support Team _ Janey

[sydney20]

The tables I put in the matrix is the worker name, region, business unit and country details. Then for values, just count (countrows, countx, distinct count) of certain data points and the source table does not have any connection to the dimensions. 

One thing I noticed is that if my dax does not have +0 at the latter part (so it would show 0 instead of balnk), the hierarchy works. But the moment I put 0, it repeats. 😞

[v-janeyg-msft]

Hi, @sydney20 

That's because you use matrix to generate values that don't exist in the original row and column.