list products in same category

[simonhobman]

I have two tables, Products and Categories (many products => one category).  My report is filtered on Product.

I want to create a calculated table to view the competing products (i.e. the other products in the same category).  Eventually I will rank them by sales, etc, but for now, I'm just piecing it together.

My first step was to create a DAX measure to give me the count of the competitors, this works great.

First Category Id = FIRSTNONBLANK(Category[Id], 1) 

Number of Competitors = 
VAR CategoryId = FIRSTNONBLANK(Category[Id], 1) RETURN
CALCULATE(
	COUNT(Products[Apir]),
	FILTER(ALL(Products), [First Category Id] = CategoryId)
)

The above result is that I get the number of products in the category, spot on. (Maybe it could be simpler???)

Next is where I'm stuck.  I want to list a table of all the competing products.  I've tried a few things but I haven't got it right. Based on what I did above, this is how I was looking at approaching the problem.

Competitor Products = 
VAR CategoryId = FIRSTNONBLANK(Category[Id], 1) RETURN
CALCULATETABLE(
	Products,
	FILTER(ALL(Products), [First Category Id] = CategoryId)
)

What I see in the table is all the products, they're not being filtered to the category.  I know i'm doing something wrong, just trying to figure this all out.

Many thanks

[v-shex-msft]

Hi @simonhobman,

Can you please share your data struct and sample data? It will be help for analysis.

Regards,

Xiaoxin sheng

[TomMartens]

Hey,

not sure abaut your data model and its relationships

I would assume that there is the following relationship: products (many) <- category (one), meaning that each product just has one category assigned and that each category has many products assigned.

Can you please confirm my understanding or explain your model in more detail.

Regards

[simonhobman]

Hi @TomMartens - you got it right. many products in one category.