Hierarchy using a dimension and a fact table field

[Anonymous]

Hello, I havea fact table with sales and two dimension tables (calendar and branches).

.pbix file below:

https://drive.google.com/file/d/1g_A6luT3Nn2Szk-oZhFR04AiPC_Yp-sV/view?usp=sharing 

I have a measure that calculates the business days passed since a salesman made a sale of an item per category per month (chosen from a slicer).

If a salesman has not made any sale at all for some category, instead of showing blank in the corresponding cell, I would like to see how many business days have passed since the start of the year (have not managed to do that yet).

For this reason I would like to show in the matrix ALL the branches with ALL their salesmen instead of only the branches that have a salesman who has also made a sale in some category.

In order to achieve that I have the following matrix but if I expand the branches, I see all the salesmen in all branches which is wrong.

This is because the "branch_name" comes from the dimension table "branches" and the "salesman_id" and "category" come from the fact table "sales".

I know that if I choose the "branch_id" from the fact table instead of the dimension table I will get the branches with their correct salesmen but I will not be able to see the branches that have no sales at all.

Is there a way to solve this?

[v-easonf-msft]

Hi , @Anonymous 

Not very clear.Can your share more details about your expected result?

For example, if the current result of branch 3 is incorrect, please provide  specific result your want  in excel for testing.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hello @v-easonf-msft 

So if I select from my slicer the month January 2020 I want to see this result (instead of the one I posted above)

desired result

So in general, when I choose a month and year from my slicers, my measure should calculate the business days that have passed since the last sale made in that month for each category per salesman.

If there is no sale made at all, I would like to see the days passed since the beggining of the year.

This is my "sales" table again for reference