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
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.