I have a country wise data in the column - How to create a Hub wise column using the country column

[Rajaganapathy]

Hi, 

I have a table which is having the number of customer support cases registered from different countries. Now I want to group those number of cases into Hubs like Europe, Asia, and Africa like that.

For that, I have created another table called which is having two columns like Hub and country.

Then I have created a relationship between these two tables  cases table - "country" to country vs Hub table (1 to many relationships)

There is also another relationship existing between the cases table - Date to Calender Date table.

I want to see 12 months of data, so I apply the date slicer first, then I select the Hub slicer - Everything works well.

But for some Hubs where there are no cases registered for some months the data is not showing, for some Hubs data is showing zero value.

I mean the table shows all the 12 months with zero value - wherever there is no case registered but for some hubs, the table is not showing the months at all whenever there is no case registered.

Any suggestion pls.

[parry2k]

@Rajaganapathy I did a recent video talking about this and I think that will get you going. check that video here  https://youtu.be/eCQck61_cmE

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Rajaganapathy]

Hi Parry,

Thanks for sharing the video, it is exactly reflecting the solution I want.

But, unfortunately, the issue is still not solved after creating a DAX measure for Force Zero.

I have doubts in the following area - Please refer screenshot.

1. The reference calendar table is not filtering according to the slicer date (If I choose dates between)

2. The objective I need to display a chart between the months - where the new customer cases are registered.

3. Then, the chart needs to be sliced according to Different Hubs across the Globe. So I have applied for another Hub Slicer.

4. Now, what happens is for some of the Hubs where there is no case registered for any of the months, the chart is skipping that month, because of no data.

5. I have applied your Dax formula but the issue is still not solved, I am not sure I am still doing some mistakes, please help.

 Chart with Missing MonthsReference calendar table not workingHub with cases for all the monthsHub with Case not for all the months

[parry2k]

@Rajaganapathy what measure you are using?

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Rajaganapathy]

Hi parry,

M123_Overall Open Cases = CALCULATE('51. Wireless_Measure'[M122_Open cases per Month],FILTER(ALL('Ref.Date'[Date]),'Ref.Date'[Date]<=MAX('Ref.Date'[Date])))

This is the measure