Forum Discussion

PiyushBQ's avatar
PiyushBQ
Helper I
6 years ago
Solved

Show Peer Group

Hi Guys,

 

I have a list of two columns.

 

Category
SKU
FruitApple
FruitPineapple
FruitStrawberry
Vegetable

Carrot

VegetableBrinjal
VegetableCauliflower

 

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

 

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      PiyushBQ
      Helper I
      6 years ago

      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:

      ItemKPI 1KPI 2
      StrawberryValueValue
      PineappleValueValue
      AppleValueValue


      Again, thanks heaps!

    • PiyushBQ's avatar
      PiyushBQ
      Helper I
      6 years ago
      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))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • PiyushBQ's avatar
      PiyushBQ
      Helper I
      6 years ago

      Thanks heaps, darlove!

      That solved my problem 🙂