Matrix view. Display records with no data for specific level of hierarchy

[skiv]

Hello everyone. Check out my puzzle 🙂

Input:

I have a structure of sales managers with 3 levels of hierarhy. Regional director->District director->Emlpoyee. Let's call this table 'Users'

Regional Director	District director	employee	Manager
Mask	John	Petr	John
Mask	Ipek	Fedor	Ipek
Mask	Ipek	Yan	Ipek
Mask	Ipek	Paul	Ipek
Mask		John	Mask
		Mask
Mask		Ipek	Mask

I can easely build a hierarchy for them wich looks like

1. Mask
   1. John
      1. Petr
   2. Ipek
      1. Fedor
      2. Yan
      3. Paul

I have another table with sales of those employee. Let's call it 'Facts'. Here it is.

Date	Employee	Sell	Client
01.01.2022	Paul	12$	it5432
01.02.2022	Yan	6$	it0301
01.02.2022	Paul	6$	it04211
01.03.2022	Yan	789$	it0984
01.05.2022	Yan	2$	it020393

It is obvious that those two tables linked via Employee column.

Let's say I have a dash with matrix structure and date slycer.

The matrix has been updated with 2 more levels of hierahy.

Director->Manager->Emlpoyee->Client->Price

From the box, the matrix in pbi will show data with Employees who sold something. Like this.

1. Mask
   1. Ipek
      1. Yan
         1. it0301
            1.  6$
         2.  it0984
            1. 789$
         3. it020393
            1. 2$
      2. Paul
         1. it5432
            1. 12$
         2. it04211
            1. 6$

We do not see nor Petr nor Fedor in the matrix because they did not sell anything.

The problem is - Business whant to see all the hierarchy of sales managers including those who did not sell anything during the period in slycer (or in 'fact' table). 

This could be solved via feature 'show values without data'. But in that case each Empliyee in matrix will have list of ALL clients and all deals from the 'fact' table. Despite the fact that it is not readable, it makes the model very slow.

Any ideas how to fix/freez the hierarchy on a specific level and do not show records w/o data for lower levels?

Thank you in advnce.

[v-henryk-mstf · Accepted solution]

Hi @skiv ,

With the correct model structure, when field values are introduced into the fact table, the values with a null row are automatically filtered.

You can select "show item with no data" in the outermost Regional Director field to show all rows.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[skiv]

The key words in your topic are "the correct model structure,".

I checked my model and found out that I have

a)many to many relationship

b) to the both side. not single

Since I fixed it (one to many and one side) it started to work as I need. Otherwise each manager will have list of all clients and prices no matter the date and data.

Thank you very much, v-henryk-mstf!!!