Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I tried to look into a couple of place but a bit stuck.
I'm trying to create a new table from existing data by filtering certain colomns, but also then want to sort the approval date assending and then remove dupllicates based on order no. so that it keeps the first and remove the next.
This is what the data and my formula looks like
Solved! Go to Solution.
@MdJ83 I have tested out the code I gave you and it should meet your expectation
Table 2 =
GROUPBY ( --groupby gets the filtered table with only OP and gives the MAX of Approval
-- by Order Type and Order No, thereby eliminating any duplicates whatsoever
FILTER ( 'Table', 'Table'[Order Type] = "OP" ), --flters only OP
[Order Type],
[Order No],
"Approval", MAXX ( CURRENTGROUP (), [Approval] )
)
If the above does not help, please try this one
Table 4 =
SUMMARIZECOLUMNS (
'Table'[Order Type],
'Table'[Order No],
FILTER ( 'Table', 'Table'[Order Type] = "OP" ),
"Approval",
CALCULATE (
MAX ( 'Table'[Approval] ),
ALLEXCEPT ( 'Table', 'Table'[Order No] )
)
)
Hi @MdJ83 ,
Add a column to raw table:
_fist = CALCULATE(MIN('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[NO.]),'Table'[type]="OP"))
Create a new table:
Table 2 = FILTER('Table','Table'[date]='Table'[_fist])
Best Regards,
Jay
Thanks so much, great idea of doing that. One problem is that in my data there is multiple PO's with the same date so it is filtering out the addtional date but still left with duplicates. I need only a single value as I'm using this in lookupvalue
I always prefer to do this kind of modelling in Power Query.
If you're open to that solution, see attached PBIX and below code.
let
Source = Orders,
#"Filtered Rows" = Table.SelectRows(Source, each ([OrderType] = "OP")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"OrderNumber", Order.Ascending}, {"Approval", Order.Ascending}}),
BufferToForceSorting = Table.Buffer(#"Sorted Rows"),
#"Removed Duplicates" = Table.Distinct(BufferToForceSorting, {"OrderType", "OrderNumber"})
in
#"Removed Duplicates"
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
The problem is a have done a lot of data manipulations in DAX that I wont show obviously in PowerQuery as it will be pretty easy to do this in power query with filter/sort/ remove duplicates.
Thas seems to work, although i want to filter to only show in Order Type = OP
Any other suggestions?
@MdJ83 try this
Table = GROUPBY ( filter(tbl,tbl[OrderType]="OP"), tbl[OrderType], tbl[Approval], "Order No", MAXX ( CURRENTGROUP (), tbl[Order No] ) )
Thanks for that but for some reason that is filtering my data from 9m rows to 1400 rows whilst I would expect at least 6m after the filter. Also it did not remove duplicate order numbers.
@MdJ83 on my phone now but can you try this else I can rep later
Table = GROUPBY ( filter(tbl,tbl[OrderType]="OP"), [OrderType], [Approval], "Order No", MAXX ( CURRENTGROUP (), [Order No] ) )
No problem at all. I think I now what the problem is. It is removing the duplicates in dates (although the code does not look like its doing that) but not the order no. What I want it to do is filter on OP, then only extract the first approval date on an OP (as there are multipe dates)
@MdJ83 I have tested out the code I gave you and it should meet your expectation
Table 2 =
GROUPBY ( --groupby gets the filtered table with only OP and gives the MAX of Approval
-- by Order Type and Order No, thereby eliminating any duplicates whatsoever
FILTER ( 'Table', 'Table'[Order Type] = "OP" ), --flters only OP
[Order Type],
[Order No],
"Approval", MAXX ( CURRENTGROUP (), [Approval] )
)
If the above does not help, please try this one
Table 4 =
SUMMARIZECOLUMNS (
'Table'[Order Type],
'Table'[Order No],
FILTER ( 'Table', 'Table'[Order Type] = "OP" ),
"Approval",
CALCULATE (
MAX ( 'Table'[Approval] ),
ALLEXCEPT ( 'Table', 'Table'[Order No] )
)
)
@MdJ83 can you try this
Table =
GROUPBY (
tbl,
tbl[OrderType],
tbl[Approval],
"Order No", MAXX ( CURRENTGROUP (), tbl[Order No] )
)
This does not seem to works as it does not recognised filer
@MdJ83 , Try to add rank column on top of it and filter for 1
filter(ADDCOLUMNS(<Your code>, "Rank", Rankx(filer('Order',[Order no] = earlier([Order no])),[Approval Date]),,asc,dense) , [Rank]=1)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |