Forum Discussion

jorgeslzr's avatar
jorgeslzr
Frequent Visitor
6 years ago
Solved

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.   [Prod...
Need Help
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    6 years ago

    @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





Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

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

 

 

 

________________________

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

jorgeslzr's avatar
jorgeslzr
Frequent Visitor
6 years ago

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.

 

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    @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