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

[Anonymous]

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

Location	Date	Item	% of total of the day
East	1/1/2012	Burger	80
West	1/1/2012	Burger	40
North	1/1/2012	Burger	30
East	1/1/2012	Fries	20
West	1/1/2012	Fries	60
North	1/1/2012	Fries	70
East	2/1/2012	Burger	40
West	2/1/2012	Burger	50
East	2/1/2012	Fries	60
West	2/1/2012	Fries	50

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).

[Fowmy · Accepted solution]

@xi-hanng

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

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",
    ""
)

________________________

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