Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |