Forum Discussion

Erikvan_D's avatar
Erikvan_D
Frequent Visitor
7 years ago
Solved

Double grouping

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 ...
  • d_gosbell's avatar
    7 years ago

    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.