Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Having a Measure be effected by a Filter

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 🙂

 

FavoriteFoodDateofRespondantCityOfRespondant
Apple, CarrotJanuaryLondon
Pear, CarrotMarchBristol
AppleAprilLondon
Pear, Cashew, EggsDecemberBirmingham

 

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.

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

BA_Pete
Super User
Super User

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:

andyismilesaway.PNG

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
v-xicai
Community Support
Community Support

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
Super User
Super User

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:

andyismilesaway.PNG

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.