Forum Discussion

TehKonnos's avatar
TehKonnos
Frequent Visitor
2 years ago
Solved

Blank columns in Dynamic Column Selection

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.