How to include an additional factor in a table to table relationship?

[twofingertyper]

Hi all,

I have a product table which lists things in levels (1, 2, 3, 4 and ALL) and then a list of Companies who also have levels (1, 2, 3, 4). 

What I am struggling with is how to make a product list that displays both the Company specific level, as well as all of the ALL products.

I have it set up as a relationship where the Company level filters the product level via a bridging table to avoid many-to-many relationship (with Company filtering Product), what I am struggling with is how to factor in an additional bit of logic so that the ALL products always show. I am currently doing this via two tables (Company specific and ALL) in the visual which isn't ideal... 

The basics of the visual are a slicer on Company, and then a table that lists out the products below - nothing fancier than that.

Thanks for any pointers on what I should be looking at doing. 

[anmolmalviya05 · Accepted solution]

Hi @twofingertyper, I hope you are doing good.

Please try below approach:
you can use a DAX measure to dynamically display "ALL" products alongside Company-specific products.

DisplayProducts =

IF(

SELECTEDVALUE('Product'[Level]) = "ALL" ||

SELECTEDVALUE('Product'[Level]) = SELECTEDVALUE('Company'[Level]),

1,

0

)

Filter your table visual by this measure (DisplayProducts = 1).

[twofingertyper]

This one works very well - thank you

[anmolmalviya05]

Great to hear that it worked !!

Please mark it as a solution so that it will be helpful for other! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

[Ritaf1983]

Hi @twofingertyper 

It sounds like you're trying to create a combined product list that always includes the "ALL" products alongside those that match the selected company level. To achieve this in Power BI without needing to use two separate tables in the visual, you can add some logic to your data model and create a custom DAX measure or calculated column.

Here’s a potential solution:

1. **Create a Calculated Column to Identify ALL Products**:
In your product table, create a calculated column that identifies whether a product is an "ALL" product or a level-specific product. For example:

```DAX
ProductCategoryType = IF('Product'[Level] = "ALL", "ALL", "Level Specific")
```

2. **Modify Your Relationship Logic**:
Keep the existing relationships where the Company level filters the Product table. To ensure that "ALL" products are always included, you need to modify the filter logic in your measure.

3. **Create a DAX Measure for the Visual**:
Create a DAX measure that returns both the "ALL" products and the level-specific products based on the selected company level. Here is an example:

```DAX
DisplayedProducts =
VAR SelectedCompanyLevel = SELECTEDVALUE('Company'[Level])
RETURN
CALCULATETABLE(
'Product',
FILTER(
'Product',
'Product'[Level] = "ALL" || 'Product'[Level] = SelectedCompanyLevel
)
)
```

4. **Use the Measure in Your Table Visual**:
Use this measure or create a calculated table with this logic to display the products in your visual. This way, the slicer on Company will filter both level-specific products and always include the "ALL" products.

By using this approach, you'll be able to create a single table visual that dynamically displays both the company-specific products and the "ALL" products, without needing two separate tables in the visual.

If this solution does not fit your needs, to better understand what you are trying to achieve, it might be helpful to create a small PBIX file with the required logic and share it via a public Google Drive link.

Let me know if you need more details or if this approach fits your scenario!

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