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 ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    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

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])

RonaldvdH's avatar
RonaldvdH
Post Patron
4 years ago

amitchandak but how does that formula or measure prevent a column from being displayed in a visual ?

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    4 years ago

    goodday amitchandak i was hoping you have an answer for me