Forum Discussion

SebaSpotti's avatar
SebaSpotti
Advocate II
2 years ago
Solved

(empty) value in slicer

Hi guys!

 

I have a problem that a I can't solve.

I have created a table in this way: 

 
Department = DISTINCT(Table1,[Functional Position]);
 
and then I add a column in this way:
 
Department = LOOKUPVALUE(Table1[SubDepartment],Table1[Functional Position],Department[Functional Position]).
 
Everything looks good and all cells are correctly populated. But when I use the [Department] or the [Functional Position] in a slicer I get also the (empty) value as a value to be selected. Why?
 
Do you have any suggestions?
 
Thank you all!
 
Need Help
Tips and Tricks
  • WishAskedSooner's avatar
    WishAskedSooner
    2 years ago

    FWIW, I deal with this issue a lot myself. Rather than write a bunch of complex DAX measures filtering out blank values, I just select the Filters pane and apply the IS NOT BLANK filter to the selected slicer/visual's respective measure. Probably not the most robust solution though because I am finding that filters sometimes go missing when a report is published to the PBI service.

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi SebaSpotti - Slight change in your formaule with addcolumn function , can you try below measure and also call the departmentfilter measure in your slicer to avoid the blank 

     

    Department =
    ADDCOLUMNS(
    DISTINCT(SELECTCOLUMNS(Table1, "Functional Position", Table1[Functional Position])),
    "SubDepartment", LOOKUPVALUE(Table1[SubDepartment], Table1[Functional Position], [Functional Position])
    )

     

    another measure is for slicer level , you can apply for non-blank values are shown in the slicer.

     

    DepartmentFiltered =
    FILTER(
    Department,
    NOT(ISBLANK(Department[Functional Position])) && NOT(ISBLANK(Department[SubDepartment]))
    )

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • SebaSpotti's avatar
      SebaSpotti
      Advocate II
      2 years ago

      Thank you rajendraongole1 .

       

      The first solution unfortunatly do not work. Neither the second one because in the two columns there are no empty values. I really don't get why (empty) appears in the slicers.

       

      Thank you anyway!!

  • WishAskedSooner's avatar
    WishAskedSooner
    Continued Contributor
    2 years ago

    FWIW, I deal with this issue a lot myself. Rather than write a bunch of complex DAX measures filtering out blank values, I just select the Filters pane and apply the IS NOT BLANK filter to the selected slicer/visual's respective measure. Probably not the most robust solution though because I am finding that filters sometimes go missing when a report is published to the PBI service.

    • SebaSpotti's avatar
      SebaSpotti
      Advocate II
      2 years ago

      Thank you WishAskedSooner. Yes, not the cleanest solution but it works.

       

      The strange things is that there are not empty values in the two columns.