Forum Discussion
Slicer - Include Blanks automatically
Hi wonka1234,
To allow users to switch between “Draft” or “Final” and always include blank values, you can follow this method.
First, create a new table in Power BI which is not connected to your main data model. This table should just have two rows: one with “Draft” and one with “Final”.You can create it using a DAX formula like DATATABLE("Status", STRING, { {"Draft"}, {"Final"} }).
Now, use this new table in a slicer so that the user can choose either “Draft” or “Final”.
Since it’s not connected to your main data directly, you need a measure to apply this selection to your visuals.
Write a DAX measure that checks if the selected value matches the “Draft_or_Final” column in your data, or if that column is blank.
If either condition is true, it will return TRUE. Then, apply this measure as a visual-level filter (set to TRUE) for your charts.
This way, only matching rows and blank rows are shown. The user doesn't have to manually select blanks, and you avoid showing duplicate values or unwanted blank options in the slicer.
Even though you have many visuals, this setup will help you maintain clean filtering across the report without too much repetition.
Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.
Thank you.
Hi wonka1234 ,
The issue is that you're expanding a one-to-many relationship where an ID can appear with both "Draft" and "Final," and this causes duplicated rows and a slicer that includes a "(Blank)" option. That blank is showing up because some IDs in your base table don't have matching entries in the Combined table. If you use the expanded table directly for slicers, you're stuck constantly deselecting "(Blank)"—not great UX. The better approach is to create a clean slicer table and write a DAX measure that respects your selection while avoiding duplicates or blanks.
First, create a separate slicer table in DAX with just the two values, "Draft" and "Final":
DraftFinalSelector = DISTINCT(Combined[Draft_or_Final])
Then, use a measure that checks the selected Draft/Final value and counts distinct IDs accordingly:
SelectedIDCount :=
VAR _selectedStatus = SELECTEDVALUE(DraftFinalSelector[Draft_or_Final])
RETURN
CALCULATE(
DISTINCTCOUNT(MainTable[ID]),
FILTER(
Combined,
Combined[Draft_or_Final] = _selectedStatus &&
Combined[ID] = MainTable[ID]
)
)
Replace MainTable with your actual source table. This measure avoids counting IDs multiple times and only includes those that match the selected value. Use this measure in your visuals instead of using Combined.Draft_or_Final directly in a slicer. That way, the slicer only offers "Draft" and "Final" with no need to babysit the "(Blank)" option every time.
Best regards,
DataNinja777 thank you. If i want to enable a user to choose between "draft" or "final" on the service, how would you do that? On the server i have the filter pane disabled.
I think another issue is that - i already have 50 visuals and i need to filter the data for either "Draft + nulls" or "Final + nulls" , where nulls/blanks are always selected.Hi wonka1234,
To allow users to switch between “Draft” or “Final” and always include blank values, you can follow this method.
First, create a new table in Power BI which is not connected to your main data model. This table should just have two rows: one with “Draft” and one with “Final”.You can create it using a DAX formula like DATATABLE("Status", STRING, { {"Draft"}, {"Final"} }).
Now, use this new table in a slicer so that the user can choose either “Draft” or “Final”.
Since it’s not connected to your main data directly, you need a measure to apply this selection to your visuals.
Write a DAX measure that checks if the selected value matches the “Draft_or_Final” column in your data, or if that column is blank.
If either condition is true, it will return TRUE. Then, apply this measure as a visual-level filter (set to TRUE) for your charts.
This way, only matching rows and blank rows are shown. The user doesn't have to manually select blanks, and you avoid showing duplicate values or unwanted blank options in the slicer.
Even though you have many visuals, this setup will help you maintain clean filtering across the report without too much repetition.
Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.
Thank you.
Hi wonka1234,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Thank you.
