Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Dmitriy
Frequent Visitor

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

‎12-28-2018 03:24 AM

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 ?

Labels:
Message 1 of 8
5,154 Views
0
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User
In response to Dmitriy

‎12-29-2018 07:27 PM

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Message 8 of 8
5,148 Views
0
7 REPLIES 7
Dmitriy
Frequent Visitor

‎12-28-2018 03:36 AM

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 ?

Message 4 of 8
5,225 Views
0
Ashish_Mathur
Super User
Super User
In response to Dmitriy

‎12-29-2018 07:27 PM

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 8 of 8
5,149 Views
0
AlB
Community Champion
Community Champion
In response to Dmitriy

‎12-28-2018 04:31 AM

Hi @Dmitriy

 

How about using CONCATENATEX( )? Something like":

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

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

Message 5 of 8
5,218 Views
Dmitriy
Frequent Visitor
In response to AlB

‎12-29-2018 01:02 AM

@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? 

Message 6 of 8
5,187 Views
0
AlB
Community Champion
Community Champion
In response to Dmitriy

‎12-29-2018 10:09 AM

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 

Message 7 of 8
5,160 Views
0
Dmitriy
Frequent Visitor

‎12-28-2018 02:25 AM

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 ?

Message 3 of 8
5,143 Views
0
Anonymous
Not applicable

‎12-30-2018 06:04 PM

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

Message 2 of 8
5,135 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All