Show Peer Group

[PiyushBQ]

Hi Guys,

I have a list of two columns.

Category

SKU

Fruit	Apple
Fruit	Pineapple
Fruit	Strawberry
Vegetable	Carrot
Vegetable	Brinjal
Vegetable	Cauliflower

I'm trying this:

- User Filters the SKU

- The sheet detects the parent category and lists out all the SKUs in that category

Example, if a user selects Apple, the sheet will list out:

- Apple

- Strawberry

- Pineapple

I've tried this in my Power BI sheet but I'm unable to get this to work.

Looking for some guidance.

Thanks in advance!

Piyush

[Anonymous]

And here's a solution based on Virtual Relationships:

https://1drv.ms/u/s!ApyQEauTSLtOgYJT8ykY-OYNbPBWlA?e=l6cnr1

Best

D

[PiyushBQ]

Thanks heaps, darlove!

That solved my problem 🙂

[Anonymous · Accepted solution]

Here's a solution based on Many2Many (higher granularities).

https://1drv.ms/u/s!ApyQEauTSLtOgYJUerVJHKwk6ECH9Q?e=7D1Ktr

The last one based on Virtual Relationships is in the making...

Best

D

[Anonymous]

Here's one way to do it:

https://1drv.ms/u/s!ApyQEauTSLtOgYJSRAUp1G2gUFwSHw?e=N7q7Xh

Bet there's another based on virtual relationships. I'll place a link to a file with the other way in a sec... Most likely you don't want to concatenate. You want to display this in a table, in a visual, so the file above shows you how to do it.

Best
D

[Greg_Deckler]

Perhaps:

Measure = 
    VAR __SKU = SELECTEDVALUE('Table'[SKU])
    VAR __Category = MAX('Table'[Category])
    VAR __Table = FILTER(ALL('Table'),'Table'[Category] = __Category)
RETURN
    CONCATENATEX(__Table,[SKU],",")

PBIX attached. 

[PiyushBQ]

@Greg_Deckler I added Unichar(10) to achieve the desired result but all it's doing is vertically stacking the string, rather than placing string values in separate rows.

Measure =

VAR __SKU = SELECTEDVALUE('Table'[SKU])

VAR __Category = MAX('Table'[Category])

VAR __Table = FILTER(ALL('Table'),'Table'[Category] = __Category)

RETURN

CONCATENATEX(__Table,[SKU],UNICHAR(10))

[PiyushBQ]

Thank you for taking the time out, Greg.

This works!

Can you please guide on structuring the layout?

An output in the matrix table would be ideal. That'll allow me to display KPIs for the rivals

Example:

Item	KPI 1	KPI 2
Strawberry	Value	Value
Pineapple	Value	Value
Apple	Value	Value

Again, thanks heaps!