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

Combining Tables or getting count of visual

I am building a report and one point of data I need is the number of IT tickets within 30 days of hire.

 

I table Staffing as follows

Email

Hire Date

Days from Start

(Calc column=Days NOW()-'Staffing Sheet'[Hire Date])

 

A

1/1/2019

 

B

1/1/2019

 

C

5/6/2019

 

D

8/3/2019

 

E

1/5/2020

 

 

I have table Total Tickets as follows

Email

Ticket ID

Created Date

Subject

A

1

1/2/2019

Ada

C

2

6/2/2019

Rgtsfgs

E

3

1/8/2020

Sdfgsd

D

4

8/25/2019

Fdgsdfg

A

5

1/15/2019

Sdfgsdfg

A

6

2/1/2020

Dfgsdf

B

7

1/20/2019

sdfgsd

 

I also have measure Ticket Creation Days From Start which is days the number of days between start date and ticket creation. 

Ticket Creation Days From Start =

DATEDIFF (

  SELECTEDVALUE( 'Staffing Sheet'[Hire Date]),

  SELECTEDVALUE('Totals Tickets'[Created Date] ),

    DAY

)

 

My tables are connected with a relationship of one to many from Staffing Email to Total Ticket Email

With that, I have created the following table visual with filter of Ticket Creation Days fom Start less than 30. The table will change based on the slicer year and month I choose.

 

Tickets within 30 days of hire

Email

ID

Ticket Creation Days from Start

A

1

1

A

5

2

A

6

5

A

9

15

A

12

20

B

7

7

B

13

18

B

14

21

C

2

23

D

4

12

D

20

28

 

I need to figure out how to have a table that looks something like this using the same parameters as less than 30 days from hire and able to change based on my slicer ticket creation date.

Email

Ticket Count

A

5

B

3

C

1

D

2

 

Thanks for any ideas!

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @lamysroe ,
 
You may add an additonal measure as shown below.
Ticket Count = CALCULATE(
    COUNTROWS(Tickets),FILTER(Tickets,[Ticket Creation Days From Start]<=30))
 
Then drag 'Staffing ' [email] and [ticket count] into the table view
test_ticket_count.PNG
You can also refer to the pbix.
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Microsoft
Microsoft

Hi @lamysroe ,
 
You may add an additonal measure as shown below.
Ticket Count = CALCULATE(
    COUNTROWS(Tickets),FILTER(Tickets,[Ticket Creation Days From Start]<=30))
 
Then drag 'Staffing ' [email] and [ticket count] into the table view
test_ticket_count.PNG
You can also refer to the pbix.
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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