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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
alpcoker
New Member

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 situation ?  I am using SSAS live connector.  I could use standard slicer or use multiple slicer (put 3 slicer to be and'd), duplicate dimension or different type of relation.

 

For that example I want to get tweets that contains #SQL and #PowerBi

 

AttributeSlicer.png

 

Thanks,

5 REPLIES 5
MattAllington
Community Champion
Community Champion

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
 


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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,

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)



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

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. 

matchingNumbers.png

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;

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.