Create a new column with filtered data from another table

[Ratax]

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

[v-jiascu-msft · Accepted solution]

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

Best Regards,
Dale

[Ratax]

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

I hope this clarifies things 🙂

[v-jiascu-msft]

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]

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 · Accepted solution]

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

Best Regards,
Dale

[Ratax]

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 · Accepted solution]

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]

Cheers mate. 🙂
really appreciate it

[v-jiascu-msft]

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

[venug20]

@Ratax

Provide Sample data.... and explain clearly about your query.....

[Greg_Deckler]

So, the image you posted, is that the Sticks table? What is the relationship between the two tables? Is the relationship one-to-many or one-to-one? Which way does it filter?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490