Forum Discussion

kellzp22's avatar
kellzp22
Frequent Visitor
6 years ago
Solved

Filter on items with no data

Hi All,

 

I have a table visual similar to the below, the visual has 'Show Items With No Data' enabled. What I would like to do is filter on the Procedure column, for all items with 'no data' only. I.e. these are NOT blanks, they simply do not have a value in the table containing this column data.

 

Thanks so much 🙂

 

Event DateNameProcedure Code
01/01/2020Blue Pink1234
02/01/2020Pink Orange 
03/01/2020Organe Yellow 
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi kellzp22 ,

     

    You can create such a measure and add it to 'Filter on this visual'.

    Not_blank_filter = 
IF(
    ISBLANK(MAX(Sheet1[Procedure Code]) ),
    0,1
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    kellzp22 , a new column like

     

    Not blank  = calculate(countrows(Table),filter(Table,not(isblank([Procedure Code]) || [Procedure Code] = "") )

    or

    Not blank  = calculate(countrows(Table),filter(Table,len([Procedure Code])>0))

     

    blank  = calculate(countrows(Table),filter(Table,(isblank([Procedure Code]) || [Procedure Code] = "") )

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi kellzp22 ,

     

    You can create such a measure and add it to 'Filter on this visual'.

    Not_blank_filter = 
IF(
    ISBLANK(MAX(Sheet1[Procedure Code]) ),
    0,1
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.