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 wanna transfer all the ratio data, but filtered in CowNo, in the intervals 8000<x<8999 and 9000<x<9999

 

  

 

 The image posted is the sticks table.
What is i want is a new table with 2 ratio tables where ratio values are filtered in the CowNo intervals 8000<x<8999 and 9000<x<9999

 

So Ratio column 1 have all the ratio values from cows with cow numbers 8000<x<8999

 

and Ratio column 2 have all the ratio values from cows with cow numbers 9000<x<9999

Kind regards Ratax

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

10 Replies

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

    Hi Ratax,

     

    Please share a complete sample file and the expected results. Filtering the CowNo could be easy, then how can we deal with many filtered values?

     

     

    Best Regards,
    Dale

  • Ratax's avatar
    Ratax
    Helper I
    7 years ago

     

     

    The columns i wanted is F and G in the new table "Statistics". .

    I hope this clarifies things :)

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

      Hi Ratax,

       

      Are there any other columns in the new table? How can we determine which values are a pair? For example, in the first line is 701 and 734. Why?

       

       

      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