cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ladyhaley
Resolver I
Resolver I

Show column header for no value (no data row)

hi team,

 

normally with dates i can use reference table or tick show item with no value. when i have data missing and this would be fine.

 

however, i have a new issue, i have a matrix is based on sites. however for last week that site did not report anything new and when i filter the matrix changes and removes the that site since the value doesnt exist at all. 

 

i try a reference table using table = distinct(table1[site]) and connect it back with 1 way cross filter and use this field instead of the orginal however it still does the same thing. 

 

im not sure how to cater for this sernario, any help would be appreciated.

 

PS. I already cater for blank values in my measures and calulated colums

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @ladyhaley 

You could see the below link,wish it is helpful for you. If your problem is still unresolved, could pls provide your pbix file ? Remember to remove the confidential information.

 

The blank row headings that show in a matrix when mappings fail

How to hide null columns in matrix table

How to Hide empty Columns in Matrix visual?

 

 

 

Best Regard

Lucien Wang

 

View solution in original post

5 REPLIES 5
v-luwang-msft
Community Support
Community Support

Hi @ladyhaley 

You could see the below link,wish it is helpful for you. If your problem is still unresolved, could pls provide your pbix file ? Remember to remove the confidential information.

 

The blank row headings that show in a matrix when mappings fail

How to hide null columns in matrix table

How to Hide empty Columns in Matrix visual?

 

 

 

Best Regard

Lucien Wang

 

sreenathv
Solution Sage
Solution Sage

The information given by you is inadequate. But in such cases where a matrix removes rows with blank values and when people do not want that row to be removed, usually people add a zero to that measure. As a result of adding zero, instead of blank(), the measure will report 0 and therefore it will be visible in the matrix with the value 0.

 

For example, 

TestMeasure = SUMX(<sometable>,<expression>) might return blank and therefore those rows with blank values will be removed from the matrix. So change the measure to the following...

 

TestMeasure = SUMX(<sometable>,<expression>)+0

Hi my calculated colum is Column = DIVIDE(value1,value2,0) which should give 0 as an alternate result.

 

I am assuming that you are using a measure in your visual. 

 

It is true that the calculated column will give an alternate value of 0. But when you use an aggregation of any column in a measure, there will be a filter context and it will bring zero only if there are some records for those websites that are visible in the matrix (based on the filter context). 

In case a website does not have data for a particular period, it does not matter whether the calculated column using DIVIDE gives 0 or not, the measure will return blank() unless you change it because there won't be any record pertaining to the filtered websites in the table for that period.

 

It will be better if you post your DAX code here so that someone can take a look and figure out what is going wrong. What I have mentioned is a general idea based on the limited info you gave.

Hi I have a table with dates and a table which has two numeric fields. 
From those two fields I have created calculated columns to calculate numbers if they meet certain conditions.  

 

After which turning them into measures using SUM(calculated1) solved for filter with the dates used from the date table. So weeks for no values will show as blank. 

 

But now when I filter for a category it will also disapear which i suspect means i have a similar issue so prob should make a reference table.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors