Count related items

[DominicBrien]

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

[cs_skit]

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?

[DominicBrien]

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

[cs_skit]

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.

[DominicBrien]

Hello, were you able to to this in direct query mode? My understanding was that table creation was not enabled in direct query mode...

What you did does a simmilar result to what I get with group by but I would also have an issue when attempting to filter on the date of cases.

Regards,

[v-ljerr-msft]

Hi @DominicBrien,

Hello, were you able to to this in direct query mode? My understanding was that table creation was not enabled in direct query mode...

What you did does a simmilar result to what I get with group by but I would also have an issue when attempting to filter on the date of cases.

---

I don't think there is an easy way to do it with Power BI currently. You may need to add a new table which contains a column of all possible values of "Count of cases"(i.e. 1~1000 or more) to your source db, and add it to your Power BI Data Mode like below first.

Then you should be able to use the formula below to create a measure to calculate the count of CaseSubject for the corresponding "Count of cases". And show the measure with the "Count of cases" column from the new added table on the report.  In this way, the result can be filtered by other columns(date field or CaseId field).

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[CaseSubject],
            "CountOfCaseID", COUNT ( Table1[CaseID] ),
            "CSub", Table1[CaseSubject]
        ),
        [CountOfCaseID] = MAX ( Table2[Count Of CaseID] )
    )
)

Here is the sample pbix file for your reference. 

Regards