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
Hi andyismilesaway ,
Based on my test, it is not supported to use CONTAINSSTRING function in SSAS Tabular model. You may try to create measures like DAX below.
IncludesApple = IFERROR(SEARCH("Apple",SELECTEDVALUE(MyTable[FavoriteFood])),-1)
SumofApple = SUMX('MyTable',[IncludesApple])
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.