Slicer - Include Blanks automatically

[wonka1234]

Hi

1) Is there a better way I do can this slicer? I dont want to click Blank every time! I want it to be one or the other (Draft or Final). and I dont want to include duplicate values.  I am expecting 3 values based on whether i select Draft or Final. Please see attached.

power query table -

visual

power query - 

2) Maybe my joining is messed up and there is a better way to append the data?

(For some reason i cannot attach my pbix file.)

[DataNinja777]

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,

[wonka1234]

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

[v-sgandrathi · Accepted solution]

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.

[v-sgandrathi]

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.

[v-sgandrathi]

Hi @wonka1234,

I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

Thank you.

[v-sgandrathi]

Hi @wonka1234,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated.