Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IntaBruce
Helper II
Helper II

Chart the number of log entries

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

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.