Having a Measure be effected by a Filter

[andyismilesaway]

Hi, I'm pretty new to PBI, so please bear with me,

I'm trying to graph some data, and am trying to use Measures, which I thought was part of a viable solution, but I've now realised my Measure is not effected by the Filter I also need to use (and I think I know why, now I've thought about it), so am looking for a bit of steer or an alternative approach. Also note, I can only connect to the data source and read, so I don't have the ability to create any new tables, rows etc.

The principal of the scenario and data is below, I've abstracted actual fields etc 🙂

FavoriteFood	DateofRespondant	CityOfRespondant
Apple, Carrot	January	London
Pear, Carrot	March	Bristol
Apple	April	London
Pear, Cashew, Eggs	December	Birmingham

I'd ultimately like to chart the frequency of individual 'FavoriteFood' per this example, which are a comma separated field. So I can show how many times Apple came up, how many times Pear came up. Note: There were only 10 options of food the respondant can choose, so not an infinite list.

I'd also like to be able to use some Filter/Slicers for this by DateofRespondent and/or CityofRespondent. The above is an example to demonstrate the principal, there are in reality more colums, rows, tables etc - but you get the gist.

My original plan was to create a new Measure, like below to add a 1 or 0 column depending on if each row contains a particular food type, like this...

IncludesApple = IF(CONTAINSSTRING(SELECTEDVALUE(MyTable[FavoriteFood]),"Apple"),1,0)

then sum these in another Measure like this...

SumofApple = sumx('MyTable',[IncludesApple])

This worked for the whole table, and I can graph this (SumofApple, SumofEggs etc), fine - however of course when I apply a filter to the visual it doesn't take effect on the Measure.

Any workarounds or other directions to solve this that people would suggest? Again, newer to PBI, so please bear with.

[Greg_Deckler · Accepted solution]

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.

[v-xicai]

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.

[BA_Pete · Accepted solution]

Hi @andyismilesaway 

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
    renFavFood

Paste 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"
) +0

This gives me the following results and can be filtered by Date/City fine:

Pete

[Greg_Deckler · Accepted solution]

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.

[andyismilesaway]

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