Forum Discussion

Ratax's avatar
Ratax
Helper I
7 years ago
Solved

Create a new column with filtered data from another table

Hello All   I want to transfer data from one table to another, and filter some of the data. i have 2 tables "Sticks" and "Statistics".   I wanna create a new column in the table Statistics. I wa...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Ratax,

     

    It's much easier now. Please try the formula below in a calculated table.

    Table =
SUMMARIZE (
    'Table1',
    'Table1'[StickiD],
    "Ratio(CowNo 8001-8999)", CALCULATE (
        MIN ( Table1[CRatio] ),
        FILTER ( 'Table1', Table1[BCowNo] > 8000 && Table1[BCowNo] < 9000 )
    ),
    "Ratio(CowNo 9001-9999)", CALCULATE (
        MIN ( Table1[CRatio] ),
        FILTER ( 'Table1', Table1[BCowNo] > 9000 && Table1[BCowNo] < 10000 )
    )
)

    Create_a_new_column_with_filtered_data_from_another_table

    Best Regards,
    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Ratax,

     

    Try this formula, please.

     

    Table =
FILTER (
    SUMMARIZE (
        'Table1',
        'Table1'[StickiD],
        "Ratio(CowNo 8001-8999)", CALCULATE (
            MIN ( Table1[CRatio] ),
            FILTER ( 'Table1', Table1[BCowNo] > 8000 && Table1[BCowNo] < 9000 )
        ),
        "Ratio(CowNo 9001-9999)", CALCULATE (
            MIN ( Table1[CRatio] ),
            FILTER ( 'Table1', Table1[BCowNo] > 9000 && Table1[BCowNo] < 10000 )
        )
    ),
    NOT ( ISBLANK ( [Ratio(CowNo 8001-8999)] ) && ISBLANK ( [Ratio(CowNo 9001-9999)] ) )
)

     

    Best Regards,
    Dale

Ratax's avatar
Ratax
Helper I
7 years ago

The 2 columns have no relations at all. Do they need a relation or is it possible just to index them

 

This example would also be a possibility

Where they are index after stick number

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Ratax,

 

It's much easier now. Please try the formula below in a calculated table.

Table =
SUMMARIZE (
    'Table1',
    'Table1'[StickiD],
    "Ratio(CowNo 8001-8999)", CALCULATE (
        MIN ( Table1[CRatio] ),
        FILTER ( 'Table1', Table1[BCowNo] > 8000 && Table1[BCowNo] < 9000 )
    ),
    "Ratio(CowNo 9001-9999)", CALCULATE (
        MIN ( Table1[CRatio] ),
        FILTER ( 'Table1', Table1[BCowNo] > 9000 && Table1[BCowNo] < 10000 )
    )
)

Create_a_new_column_with_filtered_data_from_another_table

Best Regards,
Dale

  • Ratax's avatar
    Ratax
    Helper I
    7 years ago

    I works perfectly :)

    i forgot something though :(
    There are entries that have cow number outside the interval defined ( 10000<Cownumbers<8000). Which means there are alot of empty rows. How do i remove those?

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Ratax,

       

      Try this formula, please.

       

      Table =
FILTER (
    SUMMARIZE (
        'Table1',
        'Table1'[StickiD],
        "Ratio(CowNo 8001-8999)", CALCULATE (
            MIN ( Table1[CRatio] ),
            FILTER ( 'Table1', Table1[BCowNo] > 8000 && Table1[BCowNo] < 9000 )
        ),
        "Ratio(CowNo 9001-9999)", CALCULATE (
            MIN ( Table1[CRatio] ),
            FILTER ( 'Table1', Table1[BCowNo] > 9000 && Table1[BCowNo] < 10000 )
        )
    ),
    NOT ( ISBLANK ( [Ratio(CowNo 8001-8999)] ) && ISBLANK ( [Ratio(CowNo 9001-9999)] ) )
)

       

      Best Regards,
      Dale

      • Ratax's avatar
        Ratax
        Helper I
        7 years ago

        Cheers mate. :)
        really appreciate it