Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
This one has been bugging me and I just don't get it.
I have a calculated column filter called "Fiscal Current Day", that simply looks at today's date and marks it accordingly across all years before it. It allows me to compare same day metrics over several years. Works great.
If I turn it on, it causes SOME other filters to no longer show their Values. But, if I move the other filters to "All Pages", it works again.
It must be a relationship thing, but why would it work on All Pages if that's the case? Any thoughts?
Here it is causing a 2nd filter on this page to go empty:
If I move the 2nd filter to All Pages, the Values show up:
Solved! Go to Solution.
I found it! I had a relationship between two tables that was at the Week level, they both connected by the Start Date of the week (Sunday). The filter was set BOTH, effectively removing all other days of the week. I set it to Single (Table A -> Table B) and it worked!
Hi @cassidy,
I think these blank selection should more relate to your relationships, they may exist values who not mapping with related table records.
Slicer showing Blank as a value when data does not have any blank values
If you want to fix this, you can create a calculated table to extract two side table records and use it as a bridge to link table fields.
Then you can filter on bridge table fields and the 'blank' option should not display anymore.
Bridge =
DISTINCT ( UNION ( ALL ( Table1[Column] ), ALL ( Table2[Column] ) ) )
How to Join Many to Many with a Bridge Table in Power BI
Regards,
Xiaoxin Sheng
I've put together another example that my help. The 1st section is my actual Calendar table that marks 1 or 0 based on the current day (in this case it's 10/15). The 2nd section is how a different table (Hierarch), with no direct relationship, is reacting as a filter.
CalendarDate | FiscalMonthToDate | FiscalQuarterToDate | FiscalWeekToDate | FiscalYearToDate | FiscalPriorDay | FiscalCurrentDay | FiscalPriorWeek |
10/11/2020 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
10/12/2020 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
10/13/2020 | 1 | 1 | 1 | 1 | 0 | 0 | 0 |
10/14/2020 | 1 | 1 | 1 | 1 | 1 | 0 | 0 |
10/15/2020 | 1 | 1 | 1 | 1 | 0 | 1 | 0 |
Hierarchy Filter | |||||||
Girls | Visible | Visible | Visible | Visible | Disappears | Disappears | Visible |
Boys | Visible | Visible | Visible | Visible | Disappears | Disappears | Visible |
Baby | Visible | Visible | Visible | Visible | Disappears | Disappears | Visible |
I just got some new info, it's all about Sunday!
If the time period includes a Sunday it works, which is why Week,Month,Quarter, Year to date all work. Prior Day / Current Day works if it's a Sunday.
In fact, if I choose random Dates, it must be or include a Sunday to keep the filter values showing. So strange, getting closer!
I found it! I had a relationship between two tables that was at the Week level, they both connected by the Start Date of the week (Sunday). The filter was set BOTH, effectively removing all other days of the week. I set it to Single (Table A -> Table B) and it worked!
@v-shex-msft thanks for your reply. I'm fine with the blank selection showing.
My post was about the "Yes" & "No" selections disappearing as shown in the images. Those options are available to be if the filter is on All Pages, but not on the Visual or This Page. Any idea why that happens?
User | Count |
---|---|
102 | |
91 | |
87 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |