Need help on building histogram

[f_young2018]

Newbie question. My company blocks youtube, emails, dropbox etc. thus I won't be able to watch training vedios neither upload my test file. 

If I have following data:

F_ID	D_ID	CaseID	Service_Date	Event_Date
34	34	1268595760	10/28/2012	3/17/2017 20:28
34	34	1167721755	10/28/2012	2/1/2011 15:32
34	34	1189270952	10/28/2012	6/11/2012 14:25
34	34	1157838938	10/28/2012	5/24/2010 14:14
34	34	1271928539	10/28/2012	5/2/2017 18:48
34	34	1208328814	10/28/2012	6/29/2013 2:30
34	34	1212990857	10/28/2012	10/28/2013 14:27
34	34	1286143409	10/28/2012	2/21/2018 0:10
34	34	1286354300	10/28/2012	2/24/2018 18:24
34	34	1286364230	10/28/2012	2/24/2018 18:25
34	34	1168059793	10/28/2012	2/7/2011 7:40
34	34	1148812384	10/28/2012	9/6/2009 11:21
34	34	1146986351	10/28/2012	7/26/2009 21:33
1501A	1501A	1175678621	2/26/2012	9/16/2010 1:14
1501A	1501A	1150850369	2/26/2012	6/28/2011 6:00
1501A	1501A	1143953319	2/26/2012	5/10/2011 6:37
1501A	1501A	1273502714	2/26/2012	10/27/2009 3:01
1501A	1501A	1234691539	2/26/2012	6/4/2009 16:22
1501A	1501A	1280790926	2/26/2012	5/27/2017 6:48
1501A	1501A	1293269802	2/26/2012	3/22/2015 16:02
1501A	1501A	1273093097	2/26/2012	10/3/2017 14:03
1501A	1501A	1269236863	2/26/2012	7/10/2018 19:15
1501A	1501A	1254542113	2/26/2012	8/20/2016 16:51
1501A	1501A	1254536296	2/26/2012	3/22/2015 16:02
1501A	6R21	1163538299	12/27/2012	6/20/2010 15:22
1501A	6R21	1173059549	12/27/2012	5/20/2017 3:11
1501A	6R21	1261287002	12/27/2012	3/29/2017 23:38
1501A	6R21	1234695882	12/27/2012	5/20/2016 22:18
1501A	6R21	1159251670	12/27/2012	5/20/2016 19:39
A162	11233	1150521845	2/7/2012	10/15/2009 7:26
A162	11233	1211848627	2/7/2012	9/20/2013 16:07
A162	11233	1163008126	2/7/2012	9/20/2013 14:25
A162	11233	1281904735	2/7/2012	1/11/2010 15:02
A162	2X29	1153375344	1/22/2012	1/11/2010 16:16
A162	2X29	1151015346	1/22/2012	8/31/2010 16:00
A162	2X29	1234955452	1/22/2012	1/11/2010 16:17
A162	2X29	1212687449	1/22/2012	10/30/2009 0:10
A162	2X29	1239934078	1/22/2012	4/2/2009 8:13
A162	2X29	1235060901	1/22/2012	4/2/2015 10:16
A162	2X29	1201793596	1/22/2012	10/16/2013 4:59
A162	2X29	1166198647	1/22/2012	6/7/2015 12:06
A162	A162	1211837567	7/9/2012	4/5/2012 19:58
A162	A162	1153370128	7/9/2012	1/10/2011 6:54
A162	A162	1153373149	7/9/2012	12/12/2010 14:12

This is what I want:

1. When talking about event-date and service_date, it is for each unique F_ID and D_ID combination. Do I need to cencatenate these two field? I still want to keep the two column seperate for future filtering purpose, but when I do the following count, it is for each unique combinated F_ID + D_ID, I will call Device now.

2. So for each Device, I would like to count how many events happened after its service date. It would be:

F_ID	D_ID	Event_Count after Service_Date
34	34	7
1501A	1501A	6
1501A	6R21	4
A162	11233	2
A162	2X29	3
A162	A162	0

3. Now I would like to make a graph:

    The first bar shows the total number of Devices, the second bar shows how many deivce had 0 event after service date, the third bar shows how many device had 1 event after service date, etc. ...

Thank you!

[dedelman_clng · Accepted solution]

Hi @f_young2018

I don't think you can do your full scenario with the total number of devices next to the distribution of event counts, but I can show you a (convoluted) way to get the events grouped and displayed as you require.

First add a new table to your model, called Events

Events
0 Events
1 Event
2 Events
3+ Events

Then create a calculated table to contain the summarized data, along with some concatenation work.

DeviceSummary =
SUMMARIZE (
    Devices,
    Devices[F_ID],
    Devices[D_ID],
    "Event Count", CONCATENATE (  //This is basically if nothing, give 0, if 1 or 2 give the number, if > 3 give 3+
        IF (
            ISBLANK (
                CALCULATE (
                    COUNT ( Devices[CaseID] ),
                    FILTER ( Devices, Devices[Event_Date] > Devices[Service_Date] )
                )
            ),
            0,
            IF (
                CALCULATE (
                    COUNT ( Devices[CaseID] ),
                    FILTER ( Devices, Devices[Event_Date] > Devices[Service_Date] )
                )
                    < 3,
                CALCULATE (
                    COUNT ( Devices[CaseID] ),
                    FILTER ( Devices, Devices[Event_Date] > Devices[Service_Date] )
                ),
                "3+"
            )
        ),
        " Events"
    ),
    "Device ID", CONCATENATE ( Devices[F_ID], CONCATENATE ( "-", Devices[D_ID] ) )
)

Go into your model and link Events[Events] with DeviceSummary[Event Count].

Finally, make a column chart with Events[Events] as the X-axis and DeviceSummary[Device ID] as the value (it will default to count).  On the dropdown for Events, choose "Show items with no data". Sort the visual by Events ascending and voila.

You can apply slicers on F_ID and/or D_ID to this visual and it will react accordingly, since we have those values in the summary table.

Hope this helps

David

[f_young2018]

Hi David, thank you so much!!