Forum Discussion

buxert's avatar
buxert
New Member
5 years ago
Solved

Filter or generate second table based on first table

Hi there, does somebody know how I can filter the second table visualisation based on a measure in the first table?

Situation:
The first table has the measure '#pallets move to intern' . Based on this measure per product, I need to make a list in the second table. Each row in the second table is 1 pallet. They have a relationship based on product_id.


What I need:
For each row in the first table, I need to have X amount of rows in the second table, based on the '#pallets move to intern' measure. So for the row with Product ID 575, I need 4 rows in the second table, instead of all 8. The 4 rows in the new second table will be the pallets that I have to move.

How can I accomplish that?


This is a visual of my data 

So for row with Product ID 575, 4 out of the 8 pallets need to show up in the second table.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI buxert,

    It sounds like you want to expand row table rows based on specific field values, right? If that is the case, you can take a look at the following formula to use a table to generate an expanded table.

    Expand =
VAR _range =
    GENERATESERIES (
        MINX ( ALL ( 'Sample' ), [Length] ),
        MAXX ( ALL ( 'Sample' ), [Length] ),
        1
    )
RETURN
    SELECTCOLUMNS (
        FILTER ( CROSSJOIN ( 'Sample', _range ), [Value] > 0 && [Value] <= [Length] ),
        "Index", [Index],
        "GUID", [GUID],
        "Length", [Length],
        "ex_index", [Value]
    )

    Raw table:

    Calculate table:


    Regards,

    Xiaoxin Sheng

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    buxert , I am not able to see anything common between the two tables. There needs to something which is common to filter tables with each other.

     

     

    • buxert's avatar
      buxert
      New Member
      5 years ago

      Hi amitchandak , I updated the image. The two tables have product_id in common. It is a one-to-many relationship, with 1 in the first table and many in the second table.

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        buxert , the final measure you want is still not clear to me, based on the description I can think of measure like

         

        countx(values(Table1[product_id), if([#pallets move to intern] >0, Table2[product_id], blank()))

        // 0 can change with a different number

        or

         

        countx(values(Table1[product_id), if(isblank([#pallets move to intern]) , Table2[product_id], blank()))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI buxert,

    It sounds like you want to expand row table rows based on specific field values, right? If that is the case, you can take a look at the following formula to use a table to generate an expanded table.

    Expand =
VAR _range =
    GENERATESERIES (
        MINX ( ALL ( 'Sample' ), [Length] ),
        MAXX ( ALL ( 'Sample' ), [Length] ),
        1
    )
RETURN
    SELECTCOLUMNS (
        FILTER ( CROSSJOIN ( 'Sample', _range ), [Value] > 0 && [Value] <= [Length] ),
        "Index", [Index],
        "GUID", [GUID],
        "Length", [Length],
        "ex_index", [Value]
    )

    Raw table:

    Calculate table:


    Regards,

    Xiaoxin Sheng