Double grouping

[Erikvan_D]

I'm relatively new to PowerBI and I'm trying to do "double grouping". I'm trying to accomplish the following (simplified):

I have a source table with 4 columns:

DOSSIER CLIENT  STARTDATE   ENDDATE
1           A   2017-01-01  2019-12-31
2           A   2019-06-01  2019-12-31
3           B   2018-01-01  2018-12-31
4           C   2017-01-01  2099-12-31
5           A   2019-02-01  2019-05-30
6           C   2018-04-01  2019-04-30
7           C   2017-01-01  2019-03-04
8           B   2019-02-01  2019-02-03
9           B   2015-01-01  2020-12-15
10          A   2017-01-01  2019-12-31
11          A   2018-01-01  2018-12-31
12          B   2016-06-01  2019-02-28
13          D   2019-01-01  2019-12-31
14          D   2017-01-01  2018-12-31
15          D   2019-01-01  2019-09-09

My endresult, for instance in a table visual, should be grouped on number of dossiers as well as on number of clients while maintaining the possibility to filter on date. So, when I filter on 2019-04-10 for instance I should get:

NumberOfClients NumberOfDossiers
1               3
2               2
1               1

So 1 client has 3 open files on 2019-04-10, 2 have 2 and 1 has just 1 open file.

So far I have not been able to get this working in PowerBI. Getting the number of open files for each client is no problem, but grouping that again to get the number of clients with a specific number of files I cannot get my head around. Any ideas how I should approach this?

[d_gosbell · Accepted solution]

Yes this is possible. To get this to work I first created a measure which counts the open files on a selected date.

Open Files = 
var _selectedDate = SELECTEDVALUE('Date'[Date])
var _files = FILTER(Table1,  _selectedDate > Table1[STARTDATE] && Table1[  ENDDATE] > _selectedDate)
return COUNTROWS(_files)

Then I generated a table of numbers with the following calculated table (we need this for the "NumberOfDossiers" column)

Numbers = GENERATESERIES(1,100,1)

Then I created a measure to count the number of clients with a given number of open files

No of Clients with Open Files = 
var _customerFiles = ADDCOLUMNS(values(Table1[Client]),"OpenFiles",[Open Files])
return  countrows(Filter(_customerFiles, [OpenFiles] = SELECTEDVALUE(Numbers[Value])))

Then I created a table with the Numbers[Value] column and the [No of Clients with Open Files] measure.

[Erikvan_D]

Sorry for my late reply, I tested your solution and works fine! Thanks a lot!