topic Re: Show Peer Group in DAX Commands and Tips

Show Peer Group

PiyushBQ — Tue, 10 Mar 2020 17:49:41 GMT

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

Re: Show Peer Group

Greg_Deckler — Tue, 10 Mar 2020 18:56:47 GMT

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.

Re: Show Peer Group

PiyushBQ — Tue, 10 Mar 2020 19:04:20 GMT

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!

Re: Show Peer Group

PiyushBQ — Tue, 10 Mar 2020 19:12:59 GMT

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

Re: Show Peer Group

Anonymous — Tue, 10 Mar 2020 19:48:03 GMT

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

Re: Show Peer Group

Anonymous — Tue, 10 Mar 2020 20:00:32 GMT

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

Re: Show Peer Group

Anonymous — Tue, 10 Mar 2020 21:06:32 GMT

And here's a solution based on Virtual Relationships:

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

Best

Re: Show Peer Group

PiyushBQ — Thu, 12 Mar 2020 17:27:22 GMT

Thanks heaps, darlove!

That solved my problem 🙂