Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |