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

CHALLENGING: Custom fixed slicer options even when there is no data available

Hi I have a sample scenerio data set(actual data set used has thousands of rows):

LocationDateItem% of total of the day
East1/1/2012Burger80
West1/1/2012Burger40
North1/1/2012Burger30
East1/1/2012Fries20
West1/1/2012Fries60
North1/1/2012Fries70
East2/1/2012Burger40
West2/1/2012Burger50
East2/1/2012Fries60
West2/1/2012Fries50

As you can see for date 2/1/2012, North Location has no data as it is closed for that day.

My Visual is a PieChart with legend as 'Item', details as 'Date' and values as '% of total of the day'

I hace 4 slicers, one for year, one for month, one for days to control the date, and the last slicer is for the location.

If my date slicers select 1/1/2012, my location slicer will give me 3 option(East, West and North), from there I can pick the location to show on my piechart.

But If my date slicers select 2/1/2012, my location slicer will give me 2 option(East and West because there is no data available for North). The North option disappears.

 

Now I need help for custom slicer/a way such that when my date slicer selects 2/1/2012, my location slicer will still give me the 3 options(East, West and North), and even allow me to select North. This slicer should also show the 3 location options even when date selected where all 3 locations are closed(have no data). 

So that I'm able to select 2/1/2012 and North, the piechart would be blank, and a measure with this DAX 

No data available = IF(SUM('Data'[% of the total of the day])=BLANK(), "No Data Available","") will show a message that there is no data for that configuration of all 4 slicers.
So would be really appreciative if anyone knows a way to set up this location slicer to show all 3 fixed locations even when there is no data for that particular location(egNorth location on 2/1/2012).
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: CHALLENGE: Custom fixed targeting options even when no data is available

@xi-hanng

Create an additional table for the location that will be as a slicer.

Fowmy_0-1601285595996.png


Create your measure for % as follows

Day % = 

VAR _M = 
    CALCULATE(
        SUM(Table3[% of total of the day]),
        TREATAS(
            {max('Select Location'[Location])},
            Table3[Location]
        )
    )
RETURN
_M

A measure for the label to display when no data is available

No Data = 

IF( [Day %] = BLANK(),
    "No Data Avialble",
    ""
)

Fowmy_1-1601285732428.png

Fowmy_2-1601285744161.png

________________________

If my answer was helpful, consider Accepting it as the solution to help other members find it

Click the Thumbs-Up icon if you like this answer 🙂

Youtube Linkedin

View solution in original post

1 REPLY 1
Highlighted
Community Champion
Community Champion

Re: CHALLENGE: Custom fixed targeting options even when no data is available

@xi-hanng

Create an additional table for the location that will be as a slicer.

Fowmy_0-1601285595996.png


Create your measure for % as follows

Day % = 

VAR _M = 
    CALCULATE(
        SUM(Table3[% of total of the day]),
        TREATAS(
            {max('Select Location'[Location])},
            Table3[Location]
        )
    )
RETURN
_M

A measure for the label to display when no data is available

No Data = 

IF( [Day %] = BLANK(),
    "No Data Avialble",
    ""
)

Fowmy_1-1601285732428.png

Fowmy_2-1601285744161.png

________________________

If my answer was helpful, consider Accepting it as the solution to help other members find it

Click the Thumbs-Up icon if you like this answer 🙂

Youtube Linkedin

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors