cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
f_young2018 Frequent Visitor
Frequent Visitor

Need help on building histogram

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_IDD_IDCaseIDService_DateEvent_Date
3434126859576010/28/20123/17/2017 20:28
3434116772175510/28/20122/1/2011 15:32
3434118927095210/28/20126/11/2012 14:25
3434115783893810/28/20125/24/2010 14:14
3434127192853910/28/20125/2/2017 18:48
3434120832881410/28/20126/29/2013 2:30
3434121299085710/28/201210/28/2013 14:27
3434128614340910/28/20122/21/2018 0:10
3434128635430010/28/20122/24/2018 18:24
3434128636423010/28/20122/24/2018 18:25
3434116805979310/28/20122/7/2011 7:40
3434114881238410/28/20129/6/2009 11:21
3434114698635110/28/20127/26/2009 21:33
1501A1501A11756786212/26/20129/16/2010 1:14
1501A1501A11508503692/26/20126/28/2011 6:00
1501A1501A11439533192/26/20125/10/2011 6:37
1501A1501A12735027142/26/201210/27/2009 3:01
1501A1501A12346915392/26/20126/4/2009 16:22
1501A1501A12807909262/26/20125/27/2017 6:48
1501A1501A12932698022/26/20123/22/2015 16:02
1501A1501A12730930972/26/201210/3/2017 14:03
1501A1501A12692368632/26/20127/10/2018 19:15
1501A1501A12545421132/26/20128/20/2016 16:51
1501A1501A12545362962/26/20123/22/2015 16:02
1501A6R21116353829912/27/20126/20/2010 15:22
1501A6R21117305954912/27/20125/20/2017 3:11
1501A6R21126128700212/27/20123/29/2017 23:38
1501A6R21123469588212/27/20125/20/2016 22:18
1501A6R21115925167012/27/20125/20/2016 19:39
A1621123311505218452/7/201210/15/2009 7:26
A1621123312118486272/7/20129/20/2013 16:07
A1621123311630081262/7/20129/20/2013 14:25
A1621123312819047352/7/20121/11/2010 15:02
A1622X2911533753441/22/20121/11/2010 16:16
A1622X2911510153461/22/20128/31/2010 16:00
A1622X2912349554521/22/20121/11/2010 16:17
A1622X2912126874491/22/201210/30/2009 0:10
A1622X2912399340781/22/20124/2/2009 8:13
A1622X2912350609011/22/20124/2/2015 10:16
A1622X2912017935961/22/201210/16/2013 4:59
A1622X2911661986471/22/20126/7/2015 12:06
A162A16212118375677/9/20124/5/2012 19:58
A162A16211533701287/9/20121/10/2011 6:54
A162A16211533731497/9/201212/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_IDD_IDEvent_Count after Service_Date
34347
1501A1501A6
1501A6R214
A162112332
A1622X293
A162A1620

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. ...

histogram graph.PNG

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: Need help on building histogram

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.

 

Capture.PNG

 

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

View solution in original post

2 REPLIES 2
dedelman_clng New Contributor
New Contributor

Re: Need help on building histogram

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.

 

Capture.PNG

 

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

View solution in original post

f_young2018 Frequent Visitor
Frequent Visitor

Re: Need help on building histogram

Hi David, thank you so much!!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors