Forum Discussion

skiv's avatar
skiv
Frequent Visitor
4 years ago
Solved

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

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 directoremployeeManager
MaskJohnPetrJohn
MaskIpekFedorIpek
MaskIpekYanIpek
MaskIpekPaulIpek
Mask JohnMask
  Mask 
Mask IpekMask

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.

DateEmployeeSellClient
01.01.2022Paul12$it5432
01.02.2022Yan6$it0301
01.02.2022Paul6$it04211
01.03.2022Yan789$it0984
01.05.2022Yan2$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.

  • 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.

2 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support

    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's avatar
      skiv
      Frequent Visitor

      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!!!