cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rupak_agarwal Frequent Visitor
Frequent Visitor

When all is selected or multiple values are selected in slicer, show nothing in line chart

Hi All,

 

We have below requirement in Power BI where Line chart should display data only in the case of single selection value in slicer. Please go through below cases:

 

When All is selected in slicer, Power BI Line chart should display empty.

When Multiple values are selected in above slicer, Power BI Line chart should display empty.

When single value is selected in above slicer, Chart should display corressponding data.

 

We are using Analysis services Live connection and we also need to keep multiple selections as enabled in slicer properties. Please let us know if we can accomplish this using DAX or by some other workaround.

1 ACCEPTED SOLUTION

Accepted Solutions
wynhopkins Member
Member

Re: When all is selected or multiple values are selected in slicer, show nothing in line chart

Yep, all good with HASONEFILTER 

View solution in original post

5 REPLIES 5
Super User
Super User

Re: When all is selected or multiple values are selected in slicer, show nothing in line chart

Hello @rupak_agarwal 

You can accomplish this using something like the following.  In the measure below, the value I want to display is [Total Budget] but only if a single Dates[Year Month] is selected.

Amount Filtered = 
VAR SlicerSelection = SELECTEDVALUE( Dates[Year Month] )
RETURN 
IF ( NOT ISBLANK ( SlicerSelection ), [Total budget] )

VAR SlicerSelection = SELECTEDVALUE( Dates[Year Month] ) reads the selection of the Date[Year Month] column and returns a result only if a single month is selected.  If more that one month is selected (including all months) SELECTEDVALUE( Dates[Year Month] ) returns BLANK.  Then we check if our VAR is not blank, meaning a single month is selected, and if so return the [Total Budget] measure, otherwise return BLANK.

wynhopkins Member
Member

Re: When all is selected or multiple values are selected in slicer, show nothing in line chart

Could you use

  =IF(
 HASONEVALUE (SlicerTable[SlicerColumn] ),
            [Measure],
            BLANK()
)
rupak_agarwal Frequent Visitor
Frequent Visitor

Re: When all is selected or multiple values are selected in slicer, show nothing in line chart

@wynhopkins 

 

I already tried this, but as per my understanding HASONEVALUE() on the basis of distinct values. But as it's a live connection to analysis servcies, there are many duplicate Job names for all the employees so HASONEVALUE function is returning data in chart in case of ALL is selected in slicer.

 

I have also tried HASONEFILTER() and this is working fine. I wanna double check in this case as I haven't used HASONEFILTER() previously. I just need to show data in case of single value in slicer is selected. Please update me with your suggestions if I am on a wrong track.

rupak_agarwal Frequent Visitor
Frequent Visitor

Re: When all is selected or multiple values are selected in slicer, show nothing in line chart

@jdbuchanan71 

 

I already tried this. But as it's a live connection to analysis servcies, there are many duplicate Job names for all the employees so your above DAX is returning data in chart in case of ALL or multiple values are selected in slicer.

 

I have also tried HASONEFILTER() and this is working fine. I wanna double check in this case as I haven't used HASONEFILTER() previously. I just need to show data in case of single value in slicer is selected. Please update me with your suggestions if I am on a wrong track.

wynhopkins Member
Member

Re: When all is selected or multiple values are selected in slicer, show nothing in line chart

Yep, all good with HASONEFILTER 

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 303 members 2,735 guests
Please welcome our newest community members: