Forum Discussion

sd_09's avatar
sd_09
Frequent Visitor
1 year ago
Solved

PowerBI - Matrix table blanks

Hi, 

 

I have a matrix table on PowerBI and I have columns in it from multiple data sources. I also have some filters. I want to get rid of the blanks when filters are applied etc. Is there a way to quickly fix this without creating measures for each columns or so?

 

Thanks!

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

    Hi sd_09   ,
    Thanks for reaching out to the Microsoft fabric community forum. 

    I tested the scenario using the following sample data:

     

     

    I then created a calculated column to flag rows with at least one non-blank value:

    ShowRowFlag =

    IF (

        NOT (

            ISBLANK ( OrgData1[Score1] ) &&

            ISBLANK ( OrgData1[Score2] ) &&

            ISBLANK ( OrgData1[Percentage] )

        ),

        1,

        0

    )

    I added this column as a visual-level filter, and set it to show only where ShowRowFlag = 1.

     

    And got the result like below. Please go through the pbix file

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

    Best Regards, 
    Community Support Team  

     

14 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    Are blanks among the column values or are they visible only due to invalid relationships?

    • FBergamaschi's avatar
      FBergamaschi
      Super User
      1 year ago

      In this case you need to manually filter them out. Anyway you could solve in Power Query and not see them in Power BI Desktop or in Desktop filter hem out from the filters pane

       

      If this helped, please consider giving kudos and mark as a solution

      me in replies or I'll lose your thread

      consider voting this Power BI idea

      Francesco Bergamaschi

      MBA, M.Eng, M.Econ, Professor of BI

      • sd_09's avatar
        sd_09
        Frequent Visitor
        1 year ago

        I think i incorrectly phrased my question, so there is data generically but when I filter on lets say a location, some rows dont have data. I would like to filter those rows out when data is filtered

  • kushanNa's avatar
    kushanNa
    Super User
    1 year ago

    Hi sd_09 

     

    Have you tried right-clicking on the Org column in the visual columns and unticking "Show items with no data" to see if it works for you?

     

     

  • HarishKM's avatar
    HarishKM
    Super User
    1 year ago

    sd_09 Hey,
    I will follow below steps to troubleshoot.

     

    • Ensure that filters are correctly set to exclude blanks by modifying the filter pane settings.

    • Use Power BI’s built-in options to format columns, setting them to exclude or hide blank values.

    • In the "Format" pane, adjust the settings to hide entire rows or columns with blanks under "Rows" or "Columns" visibility settings.

    • Check the relationships in your data model to ensure accurate parent-child relationships are maintained to prevent blanks.

    • Use Power Query to transform data by removing or replacing blank/null values before loading into the matrix.

     

    Thanks

    Harish KM

    If these steps help resolve your issue, your acknowledgment would be greatly appreciated.