Chart the number of log entries

[IntaBruce]

Hi,  I'm sure this ought to be really easy but I just can't figure out how to do it.

I have two tables:

The first is called Cases and has a key of Case_ID

The second is a log table to which is added a new row each time a Case record is updated and includes the Case ID

How can I produce a chart showing the distribution of numbers of logs per case?

There are over 19,000 cases so I don't want 19,000 rows or columns in my report,  I want something like a histogram with groupings of say 1-10, 11-20, and so on.

Either as a table or a chart is fine since once I have one I'm sure I can maniplate it.

Thanks in advance.   Bruce

[jdbuchanan71 · Accepted solution]

@IntaBruce 

You would create a table with your groupings in it, something like this.

Log Entries Groupings = 
DATATABLE (
    "Low", INTEGER,
    "High", INTEGER,
    "Description", STRING,
    {
        { 1, 5, "1-5" },
        { 6, 10, "6-10" },
        { 11, 10000, "11+" }
    }
)

Then you write a measure to count the number of cases that fall into each count.

Count of cases in Grouping =
VAR _Low =
    SELECTEDVALUE ( 'Log Entries Groupings'[Low] )
VAR _High =
    SELECTEDVALUE ( 'Log Entries Groupings'[High] )
VAR _CasesWithCount =
    ADDCOLUMNS (
        VALUES ( 'Cases'[Case ID] ),
        "@Logs", CALCULATE ( COUNTROWS ( CaseLog ) )
    )
RETURN
    COUNTROWS ( FILTER ( _CasesWithCount, [@Logs] >= _Low && [@Logs] <= _High ) )

I have attached my sample file for you to look at.

[IntaBruce]

Perfect, just what I needed, thank you for your solution and helpful example!