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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Removing Data from Rows Using Subtraction

Hello All,

 

I am a newish user and I have been trying to figure this out for a couple days, so I would appreciate all the help I can get.

 

Sorting by Date (Oldest to Newest), I would like to remove the last 4 entries for each Item. The Dates are different for each Item, so I couldn't just unpivot and remove the rows. The Items # and name will change so I don't think I can just separate them into their own queries? 

 

Thank you in advance for your help.

 

DateItemAmount
1/1/2019Madison             14,595
2/1/2019Madison             11,342
3/1/2019Madison             (3,412)
4/1/2019Madison             13,889
5/1/2019Madison               3,393
6/1/2019Madison             54,189
7/1/2019Madison             26,452
8/1/2019Madison               1,413
9/1/2019Madison             29,344
1/1/2019Blake             17,918
2/1/2019Blake             17,990
3/1/2019Blake             17,982
4/1/2019Blake             17,984
5/1/2019Blake        (262,889)
6/1/2019Blake             71,844
1/1/2019Atrium               5,388
2/1/2019Atrium               5,389
3/1/2019Atrium        (982,410)
4/1/2019Atrium             (3,273)
5/1/2019Atrium          104,967
6/1/2019Atrium          104,968
7/1/2019Atrium             76,594
8/1/2019Atrium             66,725
1/1/2019Daybreak          147,521
2/1/2019Daybreak          147,710
3/1/2019Daybreak          147,690
4/1/2019Daybreak          147,713
1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

In DAX you could add a calculated column to COUNT per category e.g.

Column = VAR _Item = Hotel[Item]
         VAR _Day = Hotel[Date]
RETURN  
 CALCULATE(COUNT(Hotel[Item]), FILTER(Hotel, Hotel[Date] <= _Day && Hotel[Item] = _Item ))

And then create a new table by filtering (where Column > x )

 

NewTable = FILTER(Hotel, Hotel[Column] > 4)

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

In DAX you could add a calculated column to COUNT per category e.g.

Column = VAR _Item = Hotel[Item]
         VAR _Day = Hotel[Date]
RETURN  
 CALCULATE(COUNT(Hotel[Item]), FILTER(Hotel, Hotel[Date] <= _Day && Hotel[Item] = _Item ))

And then create a new table by filtering (where Column > x )

 

NewTable = FILTER(Hotel, Hotel[Column] > 4)
Anonymous
Not applicable

thank you for your help.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.