Forum Discussion
Count related items
Hello, I have a quite basic scenario (in Direct query) but I cannot find a basic solution for it.
It only involves 2 tables and a relation between the two.
Lets say I have a Person table and a Case table Cases are about People.
So every case has a casesubject which is a foreign key to the Person table.
I want to report about number of cases for people.
So if case data looked something like this…. I simply added the date field as it is used to filter the whole report which I believe discards the group by transform option.
CaseID | CaseSubject | Date |
1 | 1 | 2010-02-28 |
2 | 2 | 2011-03-28 |
3 | 3 | 2014-02-12 |
4 | 3 | 2015-12-08 |
5 | 3 | 2013-04-13 |
6 | 2 | 2011-09-18 |
7 | 4 | 2013-01-02 |
8 | 5 | … |
9 | 6 | … |
10 | 5 | … |
11 | 6 | … |
12 | 6 | … |
13 | 7 | … |
14 | 8 | … |
15 | 8 | … |
I can easily report on number of cases per subject
CaseSubject | Count of cases |
1 | 1 |
2 | 2 |
3 | 3 |
4 | 1 |
5 | 2 |
6 | 3 |
7 | 1 |
8 | 2 |
But what I need is
Number of subject for number of cases.
Count of subjects | Count of cases |
3 | 1 |
3 | 2 |
2 | 3 |
I tried solutions to many similar posts in this forum without luck.
What have not tried yet?
Thanks.
Dominic
5 Replies
I don't exactly get what you need. Your data table seems to suggest 1 Case only has 1 subject?
Or does a case have multiple subjects because thats what the result you need seems to suggest?
Hello, a case always has a single subject.
What I am looking for is how many subject have 1, 2, 3 etc cases as per last table.
I can do it with a Group by but I have a problem when I try to apply filters using the date field...
To solve it I had to create a new table with this DAX:
then you can do this:
Didn't get it to work quickly without the extra table I don't use SUMMARIZE often.
Maybe you can figure out if you can do it without the extra table but this works.
