Dynamic columns for matrix

[CMSGuy]

Name	Certificate	Task 1	task 2	task 3	task 4	task 5	instances
Joe	A	First	Second				2
Nancy	B	First	Second	Third	Fourth		4
Joe	C	First	Second	Third	Fourth	Fifth	1
Bill	B	First	Second	Third	Fourth		7
Ted	A	First	Second				5
Ted	C	First	Second	Third	Fourth	Fifth	3

This is sample data.  This report does not have any date slicers, but it does for Name and Certificate.  These slicers can select ALL (no single select only).  The row in the matrix is the name.  The columns are the certificate name, but then I need to DYNAMICALLY create the Task Names based on the certificate selected.  So if Certificate A is selected, it would show Joe and Ted with columns First and Second.  If C were selected, it would still show Joe and Ted, but would show First, Second, Third, Fourth, Fifth.  The values being the Instances.  What can be done to accomplish this?  Thanks for any information.

Brad

[PaulDBrown · Accepted solution]

Ok, here is one (rather elaborate) way. 

First you need to create a duplicate of the original table and:

- Delete the Instances column

- Unpivot all columns except the Certificate and Name columns (select these two column, right click + "unpivot other columns".

- Filter out the blank rows under task number

Let's call this table 'Tasks Table'

 With the original table, select the Certificate, Name and instances column, right click * "Remove other columns"

Let's call this the 'Instances Table'

 Create dimension tables for Tasks, Certificate and name and join them with their corresponding fields in 'Tasks Table' and 'Instances Table' in single, one-to-many relationships. The model looks like this:

SOLUTION A:
IF you only need the one measure (for "Instances"), you can create the matrix with the fields from the dimension tables and the following measure:

Change totals label = 
IF(ISINSCOPE('Tasks Table'[Task]), MAX('Tasks Table'[Task number]), SUM('Instances Table'[instances]))

Change the label for the "total" in the formatting pane.

SOLUTION B:
If you need more than one measure, you can now create the matrix using the fields from the dimension tables and the following measures as an example:

SUM instances = 
IF(ISINSCOPE('Task Dimension'[dTask]), BLANK(), SUM('Instances Table'[instances]))

Tasks = IF(ISINSCOPE('Task Dimension'[dTask]), MAX('Tasks Table'[Task number]))

Which will get you the following:

 The caveat as you can see is that there are blank columns for the [Sum instances] measure. You can solve this by turning off word wrap for the column headers and "hiding" the rogue columns:

Again the Caveat is that this had to be done manually, so if more tasks appear, you will need to hide more columns

SOLUTION C:

Arguable a more elegant solution which is also future proof is to use a custom header layout for the matrix. You can do this by creating a new table which contains all tasks and a  column for the measures following this pattern:

Matrix headers = 
VAR _TSKS =
    ADDCOLUMNS (
        VALUES ( 'Task Dimension'[dTask] ),
        "Order", RANK.EQ ( 'Task Dimension'[dTask], 'Task Dimension'[dTask], ASC )
    )
VAR _NumberTasks =
    DISTINCTCOUNT ( 'Task Dimension'[dTask] )
VAR _Instances = { ( "Instances", _NumberTasks * 10 ), ("Average by certificate", _NumberTasks * 10+1)}
RETURN
    UNION ( _TSKS, _Instances )

(Sort the dTask field by the Order field)

Leave the Matrix Headers table unrelated. The model now looks as follows:

 Now use this 'Matrix Headers'[dTask] field for the columns in the matrix and use the following measure for the values:

Matrix value =
VAR _Index =
    DISTINCTCOUNT ( 'Task Dimension'[dTask] ) * 10
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Matrix headers'[Order] ) = _index, SUM ( 'Instances Table'[instances] ),
        AND (
            SELECTEDVALUE ( 'Matrix headers'[Order] ) = _Index + 1,
            NOT ISBLANK ( SUM ( 'Instances Table'[instances] ) )
        ),
            AVERAGEX (
                ALLSELECTED ( 'Name Dimension'[dName] ),
                CALCULATE ( SUM ( 'Instances Table'[instances] ) )
            ),
        ISINSCOPE ( 'Name Dimension'[dName] ),
            CALCULATE (
                MAX ( 'Tasks Table'[Task number] ),
                TREATAS ( VALUES ( 'Matrix headers'[dTask] ), 'Task Dimension'[dTask] )
            )
    )

and you now get:

Sample PBIX file attached

[CMSGuy]

Thank you!  I knew about this process but could not find it anyplace.  

[v-binbinyu-msft]

Hi @CMSGuy ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting .

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[CMSGuy]

Yes, my issue is depending on the filter (certificate) there can be different tasks and even number of tasks.  In my data example, Certificate A has only two tasks - First, Second, whereas Certificate B has First, Second, Third, Fourth.  Certificate C adds an additional Fifth task.  So if the end user has Certificate A selected on a slicer, the matrix will only have the three columns- First, Second, Third.  If they have selected Certificate C, then all five will be shown.  I am trying to figure out how to dynamically show the tasks that belong to the certificates.