cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Hierarchy using a dimension and a fact table field

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?

 

 

branches.PNG

 

2 REPLIES 2
Community Support
Community Support

Hi , @Dev13 

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

 

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

Dev13_1-1606823197230.png

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors