topic Re: For each group find first negative value and replace subsequent years with zero in DAX Commands and Tips

For each group find first negative value and replace subsequent years with zero

Anonymous — Thu, 02 Dec 2021 09:12:20 GMT

Hello, how can I get the first negative number for each Group, Category and Country and then make the new value for that year and years after value zero?

Group	Category	Country	Year	Value	New value
1	A	1	2018	50	50
1	A	1	2019	20	20
1	A	1	2020	-10	0
1	A	1	2021	20	0

PS: It concerns a table with about 840.000 rows. (Groups (3) x categories (20) x countries (100) x years (140). In terms of performance, it it best to do this in Power Query or in DAX? If I use a DAX measure I need to make sure the transformation is done on the group level so not aggregate countries and categories etc.

Re: For each group find first negative value and replace subsequent years with zero

VahidDM — Thu, 02 Dec 2021 12:54:50 GMT

Hi Anonymous

Try this code to add a new column to your table:

New value = VAR _CY = [Year] VAR _LY = _CY - 1 RETURN IF( [Value] < 0, 0, IF( CALCULATE( MAX( 'Table'[Value] ), FILTER( ALL( 'Table' ), 'Table'[Group] = EARLIER( 'Table'[Group] ) && 'Table'[Category] = EARLIER( 'Table'[Category] ) && 'Table'[Country] = EARLIER( 'Table'[Country] ) && 'Table'[Year] = _LY ) ) < 0, 0, [Value] ) )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: For each group find first negative value and replace subsequent years with zero

CNENFRNL — Thu, 02 Dec 2021 13:57:55 GMT

Aside from calculated column with DAX, Table.MinN() in PQ easily does the trick in your scenario. Since the large dataset is logically sliced into many small ones, I expect the performance is satisfactory.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBJDsAgCAXQu7DGBBw6LHsOw/2vUaBNuuh3Yb74VBLmJCWmy1dkFT08hpDxT84MIH7IVBSSfo/qK+P5TvISAF1BlB1By/5Iem4BRJSGZPOAsC+7xMwqmd0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Category = _t, Country = _t, Year = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Group", "Category", "Country"}, {{"ar", each let tb = Table.MinN(_, "Year", each [Value] > 0), tb0 = Table.ReplaceValue(Table.Skip(_, Table.RowCount(tb)), each [Value], 0, Replacer.ReplaceValue, {"Value"}) in tb & tb0}}), #"Combined Tables" = Table.Combine(#"Grouped Rows"[ar]) in #"Combined Tables"

Re: For each group find first negative value and replace subsequent years with zero

smpa01 — Thu, 02 Dec 2021 17:51:37 GMT

Anonymous are you getting it from a SQL server? If there is no filter context involved for this task, doing the transformation server side will give you the optimimum performnace.

For example, in TSQL

declare @t1 as table (grp int,cat varchar(10), country int, year int, value int, row int) insert into @t1 select * from ( values (1, 'A', 1, 2018, 50, 1), (1, 'A', 1, 2019, 20, 2), (1, 'A', 1, 2020,-10, 3), (1, 'A', 1, 2021, 20, 4), (1, 'B', 1, 2018, 50, 5), (1, 'B', 1, 2019,-20, 6), (1, 'B', 1, 2020,-10, 7), (1, 'B', 1, 2021, 20, 8) ) t(a, b, c, d, e, f); with cte1 as( select grp, cat, country, year, value, row, CASE when sum(value) over ( partition by grp, cat, country, year )< 0 then 0 else null end as _helper from @t1 ) select grp, cat, country, year, value, row, CASE WHEN max(_helper) over(partition by grp, cat, country order by year rows unbounded preceding ) IS NULL then value else max(_helper) over (partition by grp, cat, country order by year rows unbounded preceding ) end as Desired from cte1 order by row