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

table to list into column

Hello,

I have a table about my products, its promos, and its location. As you can see on the table below, some promos dont have a placement and some products have a placement but no promo.

 

[Product]     [Promo Title]     [Placement]

      A                   x                         1

      B                   x                          1

      C                   -                          2

      D                   -                         2

      E                   y                          -

      F                   y                          -

 

What I want is to add a column that has a list of products that share the same condition (same promo+placement, just same promo, or just same placement)

 

[Product]     [Promo Title]     [Placement]      [List]

      A                   x                         1                A,B      ->(because A and B have same promo and placement)

      B                   x                          1               A,B      ->(same as above)

      C                   -                          2               C,D      ->(C and D are equal)

      D                   -                         2                C,D

      E                   y                          -                E,F

      F                   y                          -                E,F

 

Hope this makes sense.

Thanks

1 ACCEPTED SOLUTION

@jorgeslzr

Column for Promo:

Promo Col = 

VAR PROMO = 
    CALCULATETABLE(VALUES(Table6[PRODUCT]),
        Table6[PROMO TITLE] = EARLIER(Table6[PROMO TITLE]),
        ALL(Table6)
    )
RETURN

IF( OR(Table6[PROMO TITLE]="", COUNTROWS(PROMO)<2),BLANK(),
    CONCATENATEX(
    PROMO, Table6[PRODUCT], "|"
    )
)

Column for placement

Place Col = 

VAR PROMO = 
    CALCULATETABLE(VALUES(Table6[PRODUCT]),
        Table6[PLACEMENT] = EARLIER(Table6[PLACEMENT]),
        ALL(Table6)
    )
RETURN

IF( OR(Table6[PLACEMENT]=0, COUNTROWS(PROMO)<2),BLANK(),
    CONCATENATEX(
    PROMO, Table6[PRODUCT], "|"
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube Linkedin





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

4 REPLIES 4
Fowmy
Super User IV
Super User IV

@jorgeslzr 

Add the following Column to the table:  I tested with various scenarios and it works for me, have a look at the screenshot of the results below the code.

 

Column = 

VAR PROMO = 
    CALCULATETABLE(VALUES(Table6[PRODUCT]),
        Table6[PROMO TITLE] = EARLIER(Table6[PROMO TITLE]),ALL(Table6)
    )
VAR PLACE = 
    CALCULATETABLE(VALUES(Table6[PRODUCT]),
        Table6[PLACEMENT] = EARLIER(Table6[PLACEMENT]),ALL(Table6)
    )    
VAR MAT = INTERSECT( PROMO , PLACE ) 
RETURN
IF( COUNTROWS(MAT) > 1,
    CONCATENATEX( MAT, Table6[PRODUCT], "|"),
  BLANK()
)

 

Fowmy_0-1599476399945.png

 

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn
jorgeslzr
Frequent Visitor

Hi. Thank you, it helps a lot and answers what I asked for... but as I saw the solution, I realized my prob is a bit different. Sorry.

Hope you can help. Can it be two columns depending on where  do I have info? Like the image below.

There wont be a different placement or promo on the same period.

 

Captura.PNG

@jorgeslzr

Column for Promo:

Promo Col = 

VAR PROMO = 
    CALCULATETABLE(VALUES(Table6[PRODUCT]),
        Table6[PROMO TITLE] = EARLIER(Table6[PROMO TITLE]),
        ALL(Table6)
    )
RETURN

IF( OR(Table6[PROMO TITLE]="", COUNTROWS(PROMO)<2),BLANK(),
    CONCATENATEX(
    PROMO, Table6[PRODUCT], "|"
    )
)

Column for placement

Place Col = 

VAR PROMO = 
    CALCULATETABLE(VALUES(Table6[PRODUCT]),
        Table6[PLACEMENT] = EARLIER(Table6[PLACEMENT]),
        ALL(Table6)
    )
RETURN

IF( OR(Table6[PLACEMENT]=0, COUNTROWS(PROMO)<2),BLANK(),
    CONCATENATEX(
    PROMO, Table6[PRODUCT], "|"
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube Linkedin





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

amitchandak
Super User IV
Super User IV

@jorgeslzr , Try new column in dax

concatenatex(filter(Table, [Promo Title] = earlier( [Promo Title] ) ),[Product] )

 

or "{" & concatenatex(filter(Table, [Promo Title] = earlier( [Promo Title] ) ),[Product] ) & "}"



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.