Forum Discussion
Solved
expected value
i am using two columns yearmonth and value in table visual as shown below, i need third column "expected_output" in same table as highlighted below , this third column should pick up the first non ...
This dax code should work
expected_output = VAR temp = TOPN ( 1, FILTER ( Sheet1, Sheet1[yearmonth] >= EARLIER ( Sheet1[yearmonth] ) && Sheet1[value] <> 0 ), Sheet1[yearmonth], ASC ) VAR result = MINX ( temp, Sheet1[value] ) RETURN result
Can you do this on power query side ? then it should be much easier than dax
use this M code steps
let
Source = Csv.Document(File.Contents("C:\XXXX\data.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"yearmonth", Int64.Type}, {"value", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"yearmonth", Order.Descending}}),
AddedIndex= Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
// Create a temporary column with 0/null replaced with null, others kept
AddedTemp = Table.AddColumn(AddedIndex, "TempValue", each if [value] <> null and [value] <> 0 then [value] else null),
// Fill down the cleaned column
FilledDown = Table.FillDown(AddedTemp, {"TempValue"}),
// Rename filled column
Renamed = Table.RenameColumns(FilledDown, {{"TempValue", "FilledValue"}}),
#"Sorted Rows1" = Table.Sort(Renamed,{{"yearmonth", Order.Ascending}})
in
#"Sorted Rows1"
Hi kushanNa ,
it should be in DAX
This dax code should work
expected_output = VAR temp = TOPN ( 1, FILTER ( Sheet1, Sheet1[yearmonth] >= EARLIER ( Sheet1[yearmonth] ) && Sheet1[value] <> 0 ), Sheet1[yearmonth], ASC ) VAR result = MINX ( temp, Sheet1[value] ) RETURN result
