Blank columns in Dynamic Column Selection

[TehKonnos]

Hello!
I have created a Dynamic Column Selection to use in a slicer (The slicer uses a dropdown with single select). When the user selects an option, I count all the results splitter by the values of that option. 

I want to exclude blank values, for all that options, but when I try to do that via the visual, the results affect all other options from that slicer.

In the example below, follow these steps to reproduce:
1. Check how many rows exists on the right diagram, when the filter is Shirt Number

2. Change to Coffee Preference and try to exclude Blanks from the visual on the right

3. Return slicer to Shirt Number and check how the data are affected. 

The issue is that when you exclude Blanks from the Coffee Preference, you miss the truth about other categories, in our case, Shirt Number, where the value "Large" does not exist.

Link to file: https://drive.google.com/file/d/1xevTPkEJiUiVj5z87P8CJBki5PFFoIcw/view?usp=drive_link

Thank you in advance!

[Anonymous · Accepted solution]

Hi @TehKonnos ,

Please try below steps:

1. create a new table with below dax formula

Table =
VAR tmp1 =
    SELECTCOLUMNS (
        'Raw Data',
        "Name", [Name],
        "Property", "Coffee Preference",
        "Value", [Coffee Preference]
    )
VAR tmp2 =
    SELECTCOLUMNS (
        'Raw Data',
        "Name", [Name],
        "Property", "Occupied",
        "Value", [Occupied]
    )
VAR tmp3 =
    SELECTCOLUMNS (
        'Raw Data',
        "Name", [Name],
        "Property", "Shirt Number",
        "Value", [Shirt Number]
    )
VAR tmp4 =
    UNION ( tmp1, tmp2, tmp3 )
VAR tmp5 =
    FILTER ( tmp4, [Value] <> BLANK () )
RETURN
    tmp5

2. create a measure with below dax formula

Measure = COUNT('Table'[Value])

3. visualize the data

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.