Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Customer Service Dashboard: Create Relationships between tables + 'Open Tickets'

Dear all, 


I am building a customer service dashboard right now and I already received some kind help in this forum. Now I am struggling with an issue. As you can see in the attached file, the 'Opened tickets' and 'Closed Tickets' KPIs seem to work fine when working with the date slicer. Though, they do not (or only partially), if you select single countries or business units via the other two slicers. 
I think it has to do with the relationships between tables, but Power BI does not let me create respective relationships! 
Could somebody help me out here please? 

Another issue: Right now I am calculating 'Open tickets' simply via: Opened tickets - closed tickets. But is this (especially considering that the data file will grow significantly) not too simple? Because there will be times, where the user selects e.g. Nov 1st - Nov 30th in the slicer but there are stilll open tickets from October before? 

Files: https://drive.google.com/file/d/1tE0-_5KxE1shSZsMRvIbRkqpvwC8X9_F/view?usp=sharing 

Thank you very much for your help! 

Best, 
Niklas 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Heya!

Awesome to see your dashboard growing! For the opened tickets i indeed intended only to calculate the open tickets from that specific time range.

Im not sure if you want the total open tickets there all time or that you want the slicers date + previous. for example when i select

01-11-2018 - 30-11-2018. Do you want only November + past (october etc.) or do you want past + future. (october etc to december etc. 

For the filters i indeed made some changes to the relationships as you can see below:

countrie.png

As you can see i created a new table called Country, using the following formula

Country = 
 DISTINCT(Page1[Country ])

This saves 1 instance of every country. it wont save Italy 20 times but just once  to filter on it. (same CAN be done to Business Unit)
When you make the relationshop make sure to make them single (Country filters Closed/Opened) 

Filter.png

Thats it! If there is anything unclear please let me know.

 

MilkTea

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Heya!

Awesome to see your dashboard growing! For the opened tickets i indeed intended only to calculate the open tickets from that specific time range.

Im not sure if you want the total open tickets there all time or that you want the slicers date + previous. for example when i select

01-11-2018 - 30-11-2018. Do you want only November + past (october etc.) or do you want past + future. (october etc to december etc. 

For the filters i indeed made some changes to the relationships as you can see below:

countrie.png

As you can see i created a new table called Country, using the following formula

Country = 
 DISTINCT(Page1[Country ])

This saves 1 instance of every country. it wont save Italy 20 times but just once  to filter on it. (same CAN be done to Business Unit)
When you make the relationshop make sure to make them single (Country filters Closed/Opened) 

Filter.png

Thats it! If there is anything unclear please let me know.

 

MilkTea

Anonymous
Not applicable

Hi MilkTea, 

thank you very much for your solution! Looks great! Could you please send me your file somehow? Right now I cannot really backtrace which fields you linked with each other and I e.g. got the Problem that when I link 'country' and 'open', I can only set the Cross filter direction to Single (Coluntry filters Opened).

That would be awesome, thank you very much! 
Best ,
Nik

Anonymous
Not applicable

Sure heres the file:

https://1drv.ms/u/s!AgnVgd15_ye0hBhw5oxFLUPuF3sf

 

btw, Coutry filters Opened is correct.

 

Could you explain what you exactly want with the open tickets? then ill take a look into that.

Anonymous
Not applicable

Awesome, thank you! 

Sorry, forgot about the Open Tickets:

I would like to display the amount of tickets, which are open in the selected time period via the slicer, completely independent from their creation date. Hope that makes it clear? 🙂

Best, 
Nik

Anonymous
Not applicable

Hi Nik,

 

I tried my best, but this one is complex. I wanted to get the Last date of the date slicer and count the rows in closed what fall before that date + blanks when sorting on the Ticket Number. (so i dont include all blanks)
But i could not get it to work 😞

To calculate the slicer date this code can be used:

Measure = CALCULATE(MAX(Closed[Closed]),ALLSELECTED(Closed[Closed]))

This will return the date+time of the last inserted date in the slicer.

Maybe someone else can help with this issue, im curious now.


MilkTea

Anonymous
Not applicable

That's a pity but thank you very much for trying! And for all of your other solutions. 

I will play around with your slicer function, let's see where that goes for now.

Best, 
Niklas 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.