Forum Discussion

gfdytr4's avatar
gfdytr4
Helper I
1 year ago
Solved

Avoiding blank rows in Matrix

I have the following matrix layout:

The index is placed as a row so that i can display all the values and not only first, last etc. The column is  year - week. 

Is there a way to adjust the matrix or index so that the matrix doesent have blank rows. E.g index 635 should move up to 243, 1733 should move up to 243. 

 

Concatinating the values into a single row works but then i lose the ability to conditional format font color... 

 

Need Help
  • v-csrikanth's avatar
    v-csrikanth
    1 year ago

    Hi gfdytr4 

    Thank you for reaching out to the community!

    Power BI Matrix visuals do not automatically remove rows with blanks when you're using columns (not measures) in the Values field. Unfortunately, there's no built-in way to conditionally hide rows based on blank column values when you're using actual columns instead of measures.

    The common workaround using a Has Value measure only works when you're using measures in the Values field, not raw columns. Since you're using a column, Power BI renders each row even if all column values are blank, and filtering using SELECTEDVALUE() doesn’t work as expected.

    As of now, there's no documented method from Microsoft to conditionally hide rows in this scenario. If conditional formatting is important and you want to keep individual formatting, one approach would be to reshape your data model in Power Query to remove rows that have all blanks before loading to the model.


    I hope this clarifies the limitation. If this helped, kindly mark it as the accepted solution to help others in the community.

    Best regards,
    Cheri Srikanth
    Power BI & Fabric Community Support Team

3 Replies

  • Shivu-2000's avatar
    Shivu-2000
    Responsive Resident
    1 year ago

    Hi   gfdytr4

    Power BI Matrix visuals do not collapse or remove rows that contain blanks across all column values if a field (like Index) is used as a row. 

    We can do this by using measures as: 

    • Create a measure like:

      Has Value = IF ( NOT ISBLANK(SELECTEDMEASURE()), -- Replace with your actual value measure
      1, 0 )

    • Then apply a visual-level filter:

      • Set Has Value = 1

      • This will remove rows where all values are blank, and only keep rows with at least one non-blank value.

        Feel free to ask if any quetions.
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
        Happy to help!

    • gfdytr4's avatar
      gfdytr4
      Helper I
      1 year ago

      Thanks for the reply, but i am not using any measures in my matrix. I have a column placed in my Values matrix field. If i adjust the measure to use selected value using 1 doesent change anything and 0 returns blank matrix

      Has Value = IF ( NOT ISBLANK(selectedvalue(Column I Use in Values field)), -- Replace with your actual value measure
      1, 0 )
  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    1 year ago

    Hi gfdytr4 

    Thank you for reaching out to the community!

    Power BI Matrix visuals do not automatically remove rows with blanks when you're using columns (not measures) in the Values field. Unfortunately, there's no built-in way to conditionally hide rows based on blank column values when you're using actual columns instead of measures.

    The common workaround using a Has Value measure only works when you're using measures in the Values field, not raw columns. Since you're using a column, Power BI renders each row even if all column values are blank, and filtering using SELECTEDVALUE() doesn’t work as expected.

    As of now, there's no documented method from Microsoft to conditionally hide rows in this scenario. If conditional formatting is important and you want to keep individual formatting, one approach would be to reshape your data model in Power Query to remove rows that have all blanks before loading to the model.


    I hope this clarifies the limitation. If this helped, kindly mark it as the accepted solution to help others in the community.

    Best regards,
    Cheri Srikanth
    Power BI & Fabric Community Support Team