Forum Discussion

Dmitriy's avatar
Dmitriy
Frequent Visitor
7 years ago
Solved

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

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 ?

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

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

     

    Hope this helps.

     

7 Replies

  • Dmitriy's avatar
    Dmitriy
    Frequent Visitor
    7 years ago

    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's avatar
    Dmitriy
    Frequent Visitor
    7 years ago

    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 ?

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Hi Dmitriy

       

      How about using CONCATENATEX( )? Something like":

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

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

      • Dmitriy's avatar
        Dmitriy
        Frequent Visitor
        7 years ago

        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? 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

       

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

       

      Hope this helps.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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