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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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.

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors