Need Help With Matrix

[ilTac]

Hi everyone,

i'm fearly new with Power Bi and i would like your help with this issue.

My database looks like this:

and i would like a rapresentation that shows for each sector the activities for the week so i used the matrix like this:

as you know the problem with the matrix is that if there are more than one activities in one week for sector it will show only one (for example sector one week 38).

I know i can "solve" this by adding an additional level of detail for the data (like the day of the activities) but then again if there are more then one activities in one day it will show only one and i will have to add also the time. 

My question is: there is some way to show more than one cell for week/day using the matrix without adding an other level of detail?

It has not mandatory using the matrix, another visual object cab be fine too as long as it show the corrent number of activities for week.

I hope i was clear,

Thank you very much.

[amitchandak]

@ilTac , Can concatenation is an option?

Try measure like

concatenatex(Table, Table[activities])

concatenatex(Table, Table[activities],",")

[ilTac]

@amitchandak Hi, thaks for the answer but i didn't understand well. I have to try to create a new measure in the query using those sintax?

Thank you

EDIT: the solution works! The only problem is that sometimes Activities has color code so if there are two activities in the same week with concatenatex that two activities are in the same cell and have only one color. Do you know some work around to this?

Thank you

[Anonymous · Accepted solution]

Hi @ilTac ,

Please update the formula of measure as below and check whether it can get your desired result:

Measure =CALCULATE( CONCATENATEX ( DISTINCT ( 'Table'[Activities] ), 'Table'[Activities], "," ))

or

Measure =CALCULATE( CONCATENATEX ( VALUES( 'Table'[Activities] ), 'Table'[Activities], "," ))

If the above one is not applicable for your scenario, please provide the requirement with sample data and your expected result. Thank you.

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.