Forum Discussion
Having a Measure be effected by a Filter
- 6 years ago
First, is this import mode? If so, then split your food column out and unpivot the resulting columns.
Second, I do not understand why your filter and slicers should not impact your measures. As long as they are reducing the rows within 'MyTable', then the measure should be affected.
Third, I really wouldn't use SELECTEDVALUE in that way but that's just me, I use MAX.
- 6 years ago
I prepared the table in Query Editor with the following steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyEnVUXBOLCrKL1HSUfJKzCtNLKoEsnzy81Ly85RidaKVAlITi5DU+CYWJWcAaaeizOKS/BywErA5QDHHgqLMHOy6izNSy3UUXNPTi4HyLqnJqblJqUUgYzKLcjPz0jMSc5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FavoriteFood = _t, DateofRespondant = _t, CityOfRespondant = _t]), splitColumn = Table.SplitColumn(Source, "FavoriteFood", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"FavoriteFood.1", "FavoriteFood.2", "FavoriteFood.3"}), unpivotFoods = Table.UnpivotOtherColumns(splitColumn, {"DateofRespondant", "CityOfRespondant"}, "Attribute", "Value"), remAttribute = Table.RemoveColumns(unpivotFoods,{"Attribute"}), renFavFood = Table.RenameColumns(remAttribute,{{"Value", "FavoriteFood"}}) in renFavFoodPaste into a blank query using Advanced Editor to follow my steps.
Then I created a number of measures for each fruit as follows:
_countApple = CALCULATE( COUNT(eTable[FavoriteFood]), eTable[FavoriteFood] = "Apple" ) +0This gives me the following results and can be filtered by Date/City fine:
Pete
First, is this import mode? If so, then split your food column out and unpivot the resulting columns.
Second, I do not understand why your filter and slicers should not impact your measures. As long as they are reducing the rows within 'MyTable', then the measure should be affected.
Third, I really wouldn't use SELECTEDVALUE in that way but that's just me, I use MAX.
- andyismilesaway6 years agoMicrosoft Employee
Thanks for the responses. As far as I can see I'm 'Connected Live' to the dataset.
Re the filters and slicers not effecting the Measures. My thinking was that although the filter and slicers were effecting the visualisation on the page, including a table (as in visualisation), they wouldn't be effecting the Measure calculation on 'MyTable' (data structure). I probably didnt help by mentioning a table in my initial post when I was referring to the visualisation.
Will have a look at MAX vs SELECTEDVALUE