Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Hide rows with zero or blank in columns

I have created at small model in PowerPivot. The result is shown in this pivot table.  My question is:

 

How do I hide the rows where there is zero or blank in all colums (measures)? I have shown an example below.  Rows marked with yellow should not be shown.

 

I hope some of you just can come up with a hint and then I can take it from there.......

/ Søren (Denmark)

 

PS: I guess the solution is the same whether I am working 

Need Help
  • kentyler's avatar
    kentyler
    6 years ago

    but it looks like the row you want to remove does not have all the columns blank... 
    you might start by creating measures that use ISBLANK() to return a result for each of the columns your interested in, ex. col1_blank = if(ISBLANK('some_table'[col1]),0,1)

    and then a measure that uses those measures to check all the columns

    If (col1_blank && col2_blank && col3_blank && col4_blank)

    then filter the table based on the result of that measure

    here is a suggestion from another post about how to do that

    If I understand you correctly, you should be able to use the formula below to create a new measure, then use it as a visual level filter("Sum of ALL Measures" is greater than 0) to hide row "Strawberries A" on the Table visual in your scenario. Smiley Happy

    Sum of ALL Measures =
[Begin Stock] + [Purch. today]
    + [Sales today]
    + [End Stock | Purch. tomorrow]
    + [Sales tomorrow]
    + [End Stock tomorrow]

     

2 Replies

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    You might check out this

    ALLNOBLANKROW

    1.  

    From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist.

    Syntax

    DAXCopy
    ALLNOBLANKROW( {<table> | <column>[, <column>[, <column>[,…]]]} )  

     

    • kentyler's avatar
      kentyler
      Icon for Solution Sage rankSolution Sage
      6 years ago

      but it looks like the row you want to remove does not have all the columns blank... 
      you might start by creating measures that use ISBLANK() to return a result for each of the columns your interested in, ex. col1_blank = if(ISBLANK('some_table'[col1]),0,1)

      and then a measure that uses those measures to check all the columns

      If (col1_blank && col2_blank && col3_blank && col4_blank)

      then filter the table based on the result of that measure

      here is a suggestion from another post about how to do that

      If I understand you correctly, you should be able to use the formula below to create a new measure, then use it as a visual level filter("Sum of ALL Measures" is greater than 0) to hide row "Strawberries A" on the Table visual in your scenario. Smiley Happy

      Sum of ALL Measures =
[Begin Stock] + [Purch. today]
    + [Sales today]
    + [End Stock | Purch. tomorrow]
    + [Sales tomorrow]
    + [End Stock tomorrow]