Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
4 years ago
Solved

Columns with blank value

Goodday,

 

I have a matrix with all types of materials and not all materials are used in every project.

Basically i only want to show the colums (when i use the slicer) that have values > 0

 

I can't use the normal visual filters  but im sure there is a way (based on my filter selection) to display all colums with values > 0

When i filter 2 projects and a specific material is only used in one of them i obviously show both projects. But when i filter a specific project and that material isn't used i do not want that column in my matrix.

 

Is this possible or not ?

 

Thanks in advance

Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    RonaldvdH , you want filter a column or measure (example sum(Table[value]) , for the first one filter should work

     

    example calculate(count(Table[Value]), filter(Table, not(isblank(Table[Value])) && Table[Value] >0 ))

     

    For measure, you can use filter, visual level filter, or a measure like 

     

    sumx(filter(Table, Table[Project], Table[material], "_1", sum(Table[Value]) , [_1]>0 ) ,[_1])

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    HI RonaldvdH ,

    See the below if you want ,if not ,could you pls share a sample?

    sample data:

    use the below measure:

    Measure = IF(MAX('Table'[value])=0,BLANK(),MAX('Table'[value]))

    Final out put:（when choose A,B,could view a2,and when only choose A,a2 is hidden)

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

    • RonaldvdH's avatar
      RonaldvdH
      Post Patron
      4 years ago

      v-luwang-msft Maybe im doing something wrong but your formula doesn't seem to work.

       

      Test = IF(MAX('Stuklijsten'[5x16])=BLANK(),BLANK(),MAX('Stuklijsten'[5x16]))
       
       
      Ive made a new measure based on your formula but as you can see the column still shows up in the matrix
      The data is based on an excelfile with basically the same layout as the matrix above.
       
      Ive tried changing the formula to = 0 or = "" but then i get an error because of different types of value (Integer vs Text)
       
      • v-luwang-msft's avatar
        v-luwang-msft
        Community Support
        4 years ago

        HI RonaldvdH ，

        Could you pls share your pbix file ?And remember to remove confidential data.

        I checked  my measure provided.And try to add new data as your table .

        measure:

        Measure 2 = IF(MAX(Table2[value])=0,BLANK(),max(Table2[value]))

         

        And in visual ,it still work:(type3 is hidden)

         

         

         

         

        Best Regards

        Lucien

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi RonaldvdH ,

    You need to unpivot the columns（you could create a copy table then do this) :

    = Table.UnpivotOtherColumns(#"Changed Type", {"Hoofdproject", "AP gebied naam", "2V_DAC", "12V_MDAC", "24v_LTMC", "48V_LTMC", "96V_LTMC", "192V_LTMC", "LM_EDSA-D4-48", "Woningen", "14/10 connectoren", "Markers", "14/10 eind connectoren"}, "Attribute", "Value")

      Click Ctrl + the cloumn you want to use：

    Then apply and create visual:

     

    Refer:

    https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098 

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien