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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Show defined value if slicer is not selected

Hi all!

 

I'm working with slicers and have a special need I'm not being able to solve...for now. 

 

I have a table with users of a public library: 

User IDUser Name
1Samuel
2Christine
3Marcus
4Jessica

 

Another table with the books they have checked out: 

BookUser IDCurrently_reading
The Catcher In The Rye1False
19841False
Ulysses1True
A Clockwork Orange2False
David Copperfield2False
The Picture Of Dorian Gray2True
Dracula3False
The Great Gatsby3True
Brave New World4False
The Grapes Of Wrath4True


And I have two slicers:

- one for users names

- one to check the books that these users are currently reading (or not), with values True or False.

 

And one table with the book titles for each user, with a single column.

 

What I want is the following:

 

- If the second filter is not selected (the one with values True or False), I want to be shown by default the titles with Currently_reading=true, that is, the ones that they are currently reading (in the case of Samuel it would be Ulysses, in the case of Christine, The Picture Of Dorian Gray, etc.).

 

How can I do this? I'm struggling with this problem since Wednesday and I can't find a solution. I hope you can help me!

 

Regards

6 REPLIES 6
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problems.

 

Best Regards,

Rico Zhou

AlB
Super User
Super User

@Anonymous 

The solution suggested above does work. Absolutely. See it in the attached file.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

 

 

Anonymous
Not applicable

Hi @AlB , unfortunately I'm not able to share pbix files, but I'll try to be as graphic as possible:

 

The two source files I'm using for this example are just csv files with this content: 

 

users.csv:

ID_user;Name_user
1;Samuel
2;Anna
3;Christine
4;Marcus

 

books.csv: 

Book;User ID;Currently_reading
The Catcher In The Rye;1;False
1984;1;False
Ulysses;1;True
A Clockwork Orange;2;False
David Copperfield;2;False
The Picture Of Dorian Gray;2;True
Dracula;3;False
The Great Gatsby;3;True
Brave New World;4;False
The Grapes Of Wrath;4;True

 

Once files are imported, the relationship between books and users table is through User ID:
relationship.png

My dashboard is very simple, just like this:

 

The slicer in the left contains the field Name_user from table users and the one in the right, the field Currently_reading from table books, and as you can see, the table contains the Book field and the Currenty_reading field. 

 

What I want is, when no option is checked in Currently_reading, I want only values with True to be shown (in the case of Samuel, Ulysses)

 

report.png

 

Thank you very much!

AlB
Super User
Super User

@Anonymous 

Can you ideally share a simple pbix that reproduces the issue? Or the actual pbix if possible. 

That would be fastest

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

AlB
Super User
Super User

Hi @Anonymous 

I would need some more details but you could create a measure :

Show Measure =
IF (
    CALCULATE (
        NOT ISFILTERED ( Table2[Currently Reading] ),
        ALLSELECTED ( Table2[Currently Reading] )
    ),
    IF ( SELECTEDVALUE ( Table2[Currently Reading] ), 1, 0 ),
    1
)

 and apply it as a filter to your with "Show when value is 1"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

 

Anonymous
Not applicable

Hi @AlB  ! What other  details do you need? I've tried your solutions but it doesn't seem to work 😞

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.