cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
siyue Frequent Visitor
Frequent Visitor

Option of "Concatenate labels" still does not work properly

Hi all, thank you for looking at this issue. Really appreciate if you can share your thoughts here. 

 

We have two table called: 1) Sales 2) products_categorizations. The relevant columns of each table:

Sales: product_id, product_name and customsort

Products_categorizations: id, product_category_id, product_category_name and product_id

 

We can only create many-to-many relationship based on product_id (which is not the primary key for both tables) between these two tables. 

 

Then we create a stacked column chart with using product_category_name (Products_categorizations) and product_name (Sales) as Axis, and sales amount as value. The graph is sorted by product_name based on customsort (Sales). 

 

Then disable concatenate labels does not work properly. Have tried all of the tips mentioned in previous similar issues and still cannot make it work properly. 

 

Is there any way to make this function work properly in this situation? Thank you so much!

7 REPLIES 7
Super User
Super User

Re: Option of "Concatenate labels" still does not work properly

You say that concatenate labels doesn't work, but I have no idea what your desired result is from the post.  Just that something doesn't work.

 

Could you provide screenshots of the offending visual, and a representation of how you would want it to look?

siyue Frequent Visitor
Frequent Visitor

Re: Option of "Concatenate labels" still does not work properly

Hi @Cmcmahan. Thank you for reviewing and asking more detail. 

What we would want it to look as below: 

 

2.png

Super User
Super User

Re: Option of "Concatenate labels" still does not work properly

Something like this?

snipa.PNG

 

The trick to making the axis behave as expected was to change the sort of the visual.  By default it was in the order of Sales Amount, meaning the categories couldn't be grouped meaningfully.

snipb.PNG

siyue Frequent Visitor
Frequent Visitor

Re: Option of "Concatenate labels" still does not work properly

Hi @Cmcmahan Thank you so much for the answer. I have tried this solution actually and it does not work still. 

 

I guess why your solution is not working for my case because product_category_name and product_name exist in the two different tables. Furthermore, the relationship between these two tables is many to many relationship. 

 

Super User
Super User

Re: Option of "Concatenate labels" still does not work properly

Yeah, that seems like the likely issue.  It sounds like you might be better served with a more robust data model. Many-many relationships are usually more hassle than they're worth. Is there are Products table that does use product_id as a primary key? If not, you might want to consider restructuring your data so that it does exist. 

 

 It's usually a better method to have multiple 1-many relationships in a data model rather than a single many-many. In your case, a 1-many relationship between product_categories and products and another 1-many between sales and products.  In this way, product_categories and sales are still related, but only through the product table, which allows for much easier aggregations.

 

I tried to recreate your table structure as you described it in the original post, and I have no clue how you're supposed to be given a product ID and know which sales amount goes with which product category. Every product category with the same product_id would have identical sales amounts.

Since the only relation between Sales and product_categories is the product ID, each sale has no way to indicate which category it should be aggregated under.  The quickest fix seems like it would be replacing product_id in the Sales table with the primary key of the product_categories table.  That way, each sale is linked to a specific product/category combo, and can be aggregated normally in a 1-many relationship.

 

If none of this would make sense for your data model, could you share your .pbix with us to explain why this is the case?

siyue Frequent Visitor
Frequent Visitor

Re: Option of "Concatenate labels" still does not work properly

@Cmcmahan Thank you so much for your help and guidance. Yes, it gives me a lot of reflections. Sorry i may not explain my case very well and my case actually is even complicated. Thus, i may need time to think about your suggestions. Hope can still reach you again when i have more clear thoughts. Thanks! 

Super User
Super User

Re: Option of "Concatenate labels" still does not work properly

No problem. Feel free to tag me in your next question (and link back to this one, so I have some context!)

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 385 members 3,498 guests
Please welcome our newest community members: