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
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
Solved! Go to 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
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
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
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
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
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?
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
Cheers mate. 🙂
really appreciate it
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
90 | |
82 | |
76 | |
64 |
User | Count |
---|---|
144 | |
109 | |
108 | |
100 | |
96 |