Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ilTac
Frequent Visitor

Need Help With Matrix

Hi everyone,

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

My database looks like this:

database week.jpg

 

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

 

powerBI.jpg

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.

1 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.

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@ilTac , Can concatenation is an option?

Try measure like

concatenatex(Table, Table[activities])


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

@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

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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