Forum Discussion
Slicer With Selected Terms AND'd Not OR'd
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
| HashtagKey | HashtagText |
| 1 | #SQL |
| 2 | #PowerBI |
Tweets
| TweetKey | Tweet 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
| TweetKey | HashtagKey |
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
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,
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)
- alpcoker9 years agoNew Member
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;- MattAllington9 years ago
Community Champion
Those measures are not written in DAX. It looks like MDX to me.
I have now re-read the previous post and I misunderstood what you wanted. You could try adding 2 slicers and selecting one of the options in each slicer