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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.