Text value in matrix (several text values for column group)

[Dmitriy]

I have table with information about project which I need to show by Month.
Initial Data looks like:

|   Company   |   Project    | ProjectInfo |     Month      |     Year     |
|-------------|--------------|-------------|----------------|--------------|
|  Company1   |   Project1   |    text1    |    september   |     2018     |
|  Company1   |   Project1   |    text2    |    october     |     2018     |
|  Company1   |   Project1   |    text3    |    november    |     2018     |
|  Company1   |   Project1   |    text4    |    november    |     2018     |
|  Company2   |   Project2   |    text5    |    september   |     2018     |
|  Company3   |   Project3   |    text6    |    october     |     2018     |

When I need to use text data in value of matrix, Matrix can take only 1 value for each month (first or last), but I need information of all.
Example of expected data in matrix:

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	          |             |                |     text4    |

If it doesn't possible, then duplicate new row with this project on case when I table has several ProjectInfo data in 1 month.

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	Project1  |             |                |     text4    |

How I it can be done ?

[Dmitriy]

DUBPLICATE (my previous question marked as spam)

I have table with information about project which I need to show by Month.
Initial Data looks like:

|   Company   |   Project    | ProjectInfo |     Month      |     Year     |
|-------------|--------------|-------------|----------------|--------------|
|  Company1   |   Project1   |    text1    |    september   |     2018     |
|  Company1   |   Project1   |    text2    |    october     |     2018     |
|  Company1   |   Project1   |    text3    |    november    |     2018     |
|  Company1   |   Project1   |    text4    |    november    |     2018     |
|  Company2   |   Project2   |    text5    |    september   |     2018     |
|  Company3   |   Project3   |    text6    |    october     |     2018     |

When I need to use text data in value of matrix, Matrix can take only 1 value for each month (first or last), but I need information of all.
Example of expected data in matrix:

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	          |             |                |     text4    |

If it doesn't possible, then duplicate new row with this project on case when I table has several ProjectInfo data in 1 month.

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	Project1  |             |                |     text4    |

How I it can be done ?

[Dmitriy]

I have table with information about project which I need to show by Month.
Initial Data looks like:

|   Company   |   Project    | ProjectInfo |     Month      |     Year     |
|-------------|--------------|-------------|----------------|--------------|
|  Company1   |   Project1   |    text1    |    september   |     2018     |
|  Company1   |   Project1   |    text2    |    october     |     2018     |
|  Company1   |   Project1   |    text3    |    november    |     2018     |
|  Company1   |   Project1   |    text4    |    november    |     2018     |
|  Company2   |   Project2   |    text5    |    september   |     2018     |
|  Company3   |   Project3   |    text6    |    october     |     2018     |

When I need to use text data in value of matrix, Matrix can take only 1 value for each month (first or last), but I need information of all.
Example of expected data in matrix:

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	          |             |                |     text4    |

If it doesn't possible, then duplicate new row with this project on case when I table has several ProjectInfo data in 1 month.

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	Project1  |             |                |     text4    |

How I it can be done ?

[Ashish_Mathur · Accepted solution]

Hi,

This is the result i cam up with.  You may download my PBI file from here.

Hope this helps.

[AlB]

Hi @Dmitriy

How about using CONCATENATEX( )? Something like":

CONCATENATEX( Table1,Table1[ ProjectInfo], ", ") 

or any other  delimiter of your choice instead of  ", "

[Dmitriy]

@AlB,

Thank you for answer, if I got you correctly, this will retun values in 1 row.
But it means that I can't  add color conditional formatting, because it works on whole cell and not for each position of my data inside this cell.

Does it possible to make this with such way? 

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	Project1  |             |                |     text4    |

 Or is not possible in matrix and it will be grouped by Project1 for 1 row anyway? 

[AlB]

Hi @Dmitriy

I'm afraid I don't know how to do that (or whether it can be done). Hopefully someone else can lend a hand 

[Dmitriy]

I have table with information about project which I need to show by Month.
Initial Data looks like:

|   Company   |   Project    | ProjectInfo |     Month      |     Year     |
|-------------|--------------|-------------|----------------|--------------|
|  Company1   |   Project1   |    text1    |    september   |     2018     |
|  Company1   |   Project1   |    text2    |    october     |     2018     |
|  Company1   |   Project1   |    text3    |    november    |     2018     |
|  Company1   |   Project1   |    text4    |    november    |     2018     |
|  Company2   |   Project2   |    text5    |    september   |     2018     |
|  Company3   |   Project3   |    text6    |    october     |     2018     |

When I need to use text data in value of matrix, Matrix can take only 1 value for each month (first or last), but I need information of all.
Example of expected data in matrix:

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	          |             |                |     text4    |

If it doesn't possible, then duplicate new row with this project on case when I table has several ProjectInfo data in 1 month.

                  |  september  |    october     |    october   |
Company1          |-------------|----------------|--------------|
	Project1  |    text1    |    text2   	 |     text3    |
	Project1  |             |                |     text4    |

How I it can be done ?

[v-shex-msft]

Hi @Dmitriy,

I'd like to suggest you use below measure formula to instead use projectinfo column on matrix visual:

Measure =
CALCULATE (
    CONCATENATEX ( VALUES ( Table[ProjectInfo] ), [ProjectInfo], "," ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Company] ),
    VALUES ( Table[Month] )
)

Regards,

Xiaoxin Sheng