Option of "Concatenate labels" still does not work properly

[Anonymous]

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!

[Cmcmahan]

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?

[Anonymous]

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

What we would want it to look as below: 

[Cmcmahan]

Something like this?

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.

[Anonymous]

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. 

[Cmcmahan]

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?

[Anonymous]

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

[Cmcmahan]

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