cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simonhobman Frequent Visitor
Frequent Visitor

list products in same category

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

3 REPLIES 3
Super User
Super User

Re: list products in same category

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
simonhobman Frequent Visitor
Frequent Visitor

Re: list products in same category

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

Community Support Team
Community Support Team

Re: list products in same category

Hi @simonhobman,

 

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

 

Regards,

Xiaoxin sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 159 members 1,959 guests
Please welcome our newest community members: