Forum Discussion
Filter latest data as per slicer slicer selection
I have KPI data for stores for different month and I have a month Slicer.
My data looks something like this.
Store_Number Month Score
S1 July-2019 1
S1 Aug-2019 0
If I select July, then I should get score as 1 and if I select July and Aug in the slicer, I should see just 0- since that is the score based on the last visit to the store.
Hi Anonymous ,
You could use the maximum of date to find the correct score. Here is a measure for your reference.
Measure = LOOKUPVALUE ( 'Table'[Score], 'Table'[Month], CALCULATE ( MAX ( 'Table'[Month] ), ALLSELECTED ( 'Table'[Month] ) ) )
4 Replies
- mussaenda
Community Champion
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjZU0lHyKs2p1DUyMLQEsg2VYnWgwo6l6TBRA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store_Number = _t, Month = _t, Score = _t]), #"Duplicated Column" = Table.DuplicateColumn(Source, "Month", "Month - Copy"), #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Month", type date}}), #"Duplicated Column2" = Table.DuplicateColumn(#"Changed Type", "Month", "Month - Copy - Copy"), #"Extracted Month" = Table.TransformColumns(#"Duplicated Column2",{{"Month - Copy - Copy", Date.Month, Int64.Type}}), #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month", "Month", "Month - Copy.1"), #"Extracted Year" = Table.TransformColumns(#"Duplicated Column1",{{"Month - Copy.1", Date.Year, Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Extracted Year", {{"Month - Copy.1", type text}, {"Month - Copy - Copy", type text}}, "en-GB"),{"Month - Copy.1", "Month - Copy - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged") in #"Merged Columns"Based on the data you provided, you can try the code above:
- v-eachen-msft
Community Support
Hi Anonymous ,
You could use the maximum of date to find the correct score. Here is a measure for your reference.
Measure = LOOKUPVALUE ( 'Table'[Score], 'Table'[Month], CALCULATE ( MAX ( 'Table'[Month] ), ALLSELECTED ( 'Table'[Month] ) ) )
