Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ratax
Helper I
Helper I

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

 

  

 Skærmklip.png

 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

2 ACCEPTED SOLUTIONS

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Ratax
Helper I
Helper I

Skærmklip.png

 

 

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

I hope this clarifies things 🙂

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Skitse.png

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

Skærmklip.png

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Cheers mate. 🙂
really appreciate it

v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
venug20
Resolver I
Resolver I

@Ratax

 

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

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.