How to visualize '1 to n relationship' (?) sensefully in PowerBI visuals

[Catrice]

Actually, the requirement sounds very simple, but when looking for a suitable table structure and visualization in Power BI, I am a bit perplexed and therefore need a tip.
I have x customers and y business segments, whereby a customer can work in one or more business segments.
Ultimately, I would like a PowerBI visualization that filters out all applicable customers depending on the single or multiple selection. But if I only have one column for the business segment, then - if a customer works in several segments - I have to write them all as values in the corresponding cell (e.g. comma-separated). How can I then correctly represent this column in a slicer visual?
On the other hand, if I create a separate column for each segment, I don't get multiple columns visualized in one slicer visual.

PS: My table structure is not specified, I can adjust it at any time, if it helps.

Sorry for much words, The attached pictures may better illustrate my problem.

This is my table:

https://drive.google.com/file/d/1xF2gWjTRmgVTnMIFrgCRPzw1nwz-jRvh/view?usp=sharing

This would be my desired PowerBI visualization:

https://drive.google.com/file/d/1gaX_v9AkA1jYGRLXOMyjq16haENmayZx/view?usp=sharing

[v-alq-msft · Accepted solution]

Hi, @Catrice 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

There is no relationship between two tables. You may create a measure as below.

Visual Control = 
IF(
    COUNTROWS(
        FILTER(
            DISTINCT(Test[Business seg]),
            CONTAINSSTRINGEXACT(SELECTEDVALUE('Table'[Business Segment]),[Business seg])
        )
    )>0,1,0
)

Finally you may put the measure in the visual level filter to display the result.

Best Regards

Allan

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

[Catrice]

Hi ryan_mayu, Hi v-alq-msft,

thanks a lot for your contribution, it helped me to find the solution.

Indeed both proposed ideas could be a solution.

But the sole unpivot of my main table (Excel) is barely possible, cause it is a complex one with a lot of already implemented PowerBI reports and even some custom-specific macros for adding new entries etc...

Simply adding Additional columns would be possible, but unpivot the table would lead to complex, work-intensive re-structuring. This is why the solution with separate table and relation between both is the smarter one in my eyes. Again - thanks a lot!

[Catrice]

Hi guys,

I'm sorry, but I'm very confused with the structure/handling of the Power BI Community forum. Last week I accepted the solution from v-alq-msft already, now I get another request to do so via Email in SPANISH language. Also when I try to navigate in the Forum I cannot find any button which simply shows/filters me all topics created by me.

Hm,.... I'm too stupid to move around the web ??

[ryan_mayu]

@Catrice 

maybe you can try this

1. unpivot table

2. filter value=1

Then create tables and filters in the visual