non-strict filtering

[Anonymous]

Hello, 

I work with products. Each product can be in many colors.

I have two tables: product and product_color.

 - The product table contains unique products.

 - The product_color table contains product_id from the product table and the color name of that product.

What I have to show is a list of all products (one row for a product), and all the colors the product has. One product - One row. I can achieve that with the concatenatex function. 

At the same time I need to have a slicer where I can filter the products by the color, and the above list has to display the products that contain that color, i.e. if a product comes in Red and Blue, and I filter by Red, I need to show all the products that have Red, and at the same time show all their possible colors.

Could anyone recommend what the best data structure and relationships would be for such an issue? I can add all colors combinations to the product table, and it should work, but I would not want to overload the Product table with duplicate data.

Thanks!

create table product(
id integer,
name varchar(10)
);

create table product_color(
product_id integer,
color varchar(100)
);

insert into product
select 1, 'car' union all
select 2, 'phone';

insert into product_color
select 1, 'black' union all
select 1, 'white' union all
select 1, 'green' union all
select 2, 'black' union all
select 2, 'red'

[amitchandak]

@Anonymous , try like

calculate(concatenatex(filter(productcolor , productcolor[product] in _prd),productcolor[color]),removefilters(productcolor[color]))

[amitchandak]

@Anonymous , if the above does not work, Then you have consider independent color table

[amitchandak]

@Anonymous , Try a measure like

measure =
var _prd = allselected(Product[product])
return
concatenatex(filetr(all(productcolor]) , productcolor[product] in _prd),productcolor[color])

[Anonymous]

Thank you. I finally made it work, but I am still facing an issue.
I seems that the filter from product_color does not propagate onto the product table.

The measure looks like this: 

Available Colors =
VAR AffectedProducts = SELECTCOLUMNS(ALLSELECTED('product_color'), "aff_prod_ids", [product_id])

RETURN
CONCATENATEX(FILTER(ALL(product_color), product_color[product_id] IN AffectedProducts), product_color[color], "; ")

The way I understand the measure works is this:

 - product_color has two columns: "product_id" and "color";
 - I fiter "color" thru a slicer;
 - I get the "product_id"s affected by that filtering:
SELECTCOLUMNS(ALLSELECTED('product_color'), "aff_prod_ids", [product_id])
- then I cancel all the filters on product_color table and get all the product_color records for the product_ids from above:
FILTER(ALL(product_color), product_color[product_id] IN AffectedProducts)

But when I put the result of the concatenation onto the table visual, PBI does not see any connection between the product table and the measure. The measure returns the concatenated colors, but does not differentiate between the products

This picture is just an example of what I get. Even though I have only the "car" product in green, I get "phone" returned with all the car's colors, too. Any ideas why this is happening, please?