Measurement between Selected Dates

[Anonymous]

Okay, I need a little guidance here as I am new to powerbi. I'm not super familiar with DAX, but trying to figure out how I would achieve this problem.

I have 2 tables. One is a survey table, the other is a ticket table. Survey table has a date for when the survey was taken and Ticket table has a column with the creation date and the close date. What I'm trying to find, is the count of tickets that were open during the time of the survey. 

The reason why, is that I have a visual map that shows the area where the surveys are with how many tickets are there. Because the 2 data sets don't have many relationships as it's not pertaining to the particular person, the only relationship I have setup is the ZIP that way it's showing only tickets where surveys were taken on the map. Map then can be filtered through the slicer for dates.

I'm going to continue researching this to see if I can figure it out as I am brand new to this program, but figured I'd ask around since I have been getting stuck (I'm also not familiar with some of the terms people are using so I'm getting stuck that way too). 

Thank you for your time

Tables: 

'Tickets-SQL'[CreationDate] and [ResolvedDate]

'Surveys-SQL'[Date]

Relationship is through ZIP, nothing else can be used for relationship on the 2 tables. 

Update: Still a bit stuck, but got a little further than I was. 

I was able to create a count, which is great! Now it shows how many tickets were open on that date, etc etc etc. Now the only problem is, that I can't enter that into the map due to it not having a zip tied to it, (using it as the size operator), so it just shows each area shows the same amount of tickets. 

I was thinking maybe I can create columns with each zip in the rows for Surveys, thinking deeper into it, that would not work as powerbi counts the columns not the rows. Thereforth meaning that it would not work using this table lay out. If there is a way to count rows instead maybe that's an easier option, just not familiar enough. 

[Anonymous]

Complaint_ID	Creation_Date	Resolved_Date	Zip
123456789	06/01/2021	06/03/2021	83686
234567891	06/02/2021	06/14/2021	83686
345678912	06/04/2021	06/13/2021	83686
567891234	06/05/2021	06/10/2021	83686
678912345	06/06/2021	06/07/2021	83686
789123456	06/07/2021	06/22/2021	83686
891234567	06/08/2021	06/15/2021	83686

Survey_ID	Zip	Date
5555313	83686	06/07/2021
5555314	83686	06/12/2021

@Ashish_Mathur @v-henryk-mstf 

Apologies for the late reply, I was not at work and it's tied to my work email so I didn't see the response till today.

I gave some tester data to help get what I'm tyring to do. So if you create a map with this information in power bi on different tables (Can't combine tables because it would duplicate some information) then you create a relationship between the zipcodes that way you can put surveys as a tool tip and complaints (count) as the size, the zipcode that I have there (I used mine to make it easy) will always show that there are 7 tickets open as I am using the date slicer for the surveys to filter the map. The map location is based off of the Zip of the surveys that way it doesn't show any tickets that are elsewhere in the US

I have tried to create another slicer with the date for tickets to just mark it as "Tickets opened between" that way I can at least filter the map to manually show that information. It appears to help update some of the tables that I show on the right side, but on the map itself, it still shows that zipcode has 7 tickets open (in my example here) no matter what dates I have selected so it's not filtering out that date for the tickets, just the surveys. Due to the relationship between the tables, I'm not able to create a calendar table and link both dates to that and this is where I'm getting stuck even with my multiple attempts to try and correct this. 

Apologies if this doesn't make a whole lot of sense, day is pretty jam packed and just typing this as I get time 🙂 Thank you for your help! 

[Ashish_Mathur]

Hi,

Based on the 2 tables that you have shared, please show the expected result very clearly in a Table format.

[v-henryk-mstf]

Hi @Anonymous ,

According to your description, as @Ashish_Mathur  said, I hope you clearly describe your needs and provide corresponding test data (delete sensitive information), so that I can do further testing.

 Looking forward to your reply.

Best Regards,
Henry

[Ashish_Mathur]

Hi,

Share data in a form that can be pasted in an MS Excel file and show the expected result.