Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |