Forum Discussion

andyismilesaway's avatar
andyismilesaway
Microsoft Employee
6 years ago
Solved

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...
  • Greg_Deckler's avatar
    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.

  • BA_Pete's avatar
    6 years ago

    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