Forum Discussion

alpcoker's avatar
alpcoker
New Member
9 years ago

Slicer With Selected Terms AND'd Not OR'd

Hello,   I am using Attribute Slicer custom visual but as they said: All selected terms are OR'd together. AND is not an available option currently.     Are there any workaround for these situati...
Need Help
Tips and Tricks
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
9 years ago

A slicer is used on a column. E.g., the adventure Works database has a column called category, with possible values bikes, clothing, accessories and categories. If the slicer was a logical and, it would be like saying "show me all products that are both a "Bike" and also "clothing". It is impossible to be both, so a logical and would always return nothing. Given this, I don't understand the use case for a logical and using a slicer on a column. 

Spoiler
 
  • alpcoker's avatar
    alpcoker
    New Member
    9 years ago

    Thank you for your attention.

     

    There isn't any example of that on AdventureWorks DW. But I can try to explain my use case with twitter dataset used on example of Attribute Slicer Custom Visual.

     

    Hashtags

    HashtagKeyHashtagText
    1#SQL
    2#PowerBI

     

    Tweets

    TweetKeyTweet Text
    1#SQL 2016 SP1 is out
    2#PowerBI Desktop November update released
    3#PowerBI on premise only works with #SQL Analysis Service live query for now. 

     

     

    IntermediateTable 

    TweetKeyHashtagKey
    11
    22
    31
    32

     

     

    If I select single hashtag there is no problem but if I select #SQL and #PowerBI with slicer or attribute slicer I will get all tweets. (1,2,3). But in my case I just want to list only tweet #3 that contains #SQL and #PowerBI together.

     

    Thanks,

    • MattAllington's avatar
      MattAllington
      Icon for Community Champion rankCommunity Champion
      9 years ago

      You therefore need to write a measure that returns a non blank result when your pattern is matched.  In your sample data, something like this

       

      =if(distinctcount(table[hashtagkey])=distinctcount(table[tweetkey]),1)

      • alpcoker's avatar
        alpcoker
        New Member
        9 years ago

        Thank you for your help.

         

        As you suggest, I create Distinct Count of Hashtag Key measure but I can't get how many numbers of hashtag selected on attribute slicer while creating them.

         

        So I create Matching1, Matching2, Matching3 measure to check distinct count value as a temporary solution. 

        You suggest to check equality of distinct counts of HashtagKey and TweetKey. But should be HashtagKey and TweetHashtagKey ? If I could check Number of Hashtag Selected = Distinct Count of Hashtag Key, one measure would be enough?  Is there any way to get the count of selected dimensions when writing measure, or any ideas on these screenshot to make it done with one measure?

         

        Thanks,

         

        My measures are listed as follows;

         

        CREATE MEMBER CURRENTCUBE.[Measures].MatchingAll
        AS iif([Measures].[Tweet Hashtag Count] > 0, iif([Measures].[Hashtag Key Distinct Count] = [Measures].[Tweet Hashtag Count], 1, NULL), NULL),
        VISIBLE = 1;

         

        CREATE MEMBER CURRENTCUBE.[Measures].Matching1
        AS iif([Measures].[Hashtag Key Distinct Count] = 1, 1, NULL),
        VISIBLE = 1;


        CREATE MEMBER CURRENTCUBE.[Measures].Matching2
        AS iif([Measures].[Hashtag Key Distinct Count] = 2, 1, NULL),
        VISIBLE = 1;


        CREATE MEMBER CURRENTCUBE.[Measures].Matching3
        AS iif([Measures].[Hashtag Key Distinct Count] = 3, 1, NULL),
        VISIBLE = 1;