Measure causes entire row to disappear from table visual, unless we use "Show items with no data"

[SevsBo]

Strange issue here, with cause below, but first an explanation:

The columns are mostly from original data set with one being a measure using SUMX and LOOKUPVALUE to convert currencies.

I noticed we were missing data from our table visual compared to totals calulated in other visuals. To make sure no filters were active I started a new blank page and verified on Data View that the columns I would select would have data for the row in question.

After populating the columns in the table I selected one filter for the column I wanted to filter, and I was able to select the item from the list, but the moment I did that, the table went blank.

I tried toggling on "Show items with no data", and that brought back the column, and the strange part is, I can enable that on any column apart from the measure, and the row appears again. Unfortunately this can also cause some data to be duplicated.

Wondering what was happening, I started removing the few columns I had, and the first one proved to be the cause - when I removed the measure from the columns it immediately brought back the row, even when "Show items with no data" was not selected.

The measure in this particular row of data resulted in a blank cell, instead of value conversion, so clearly the measure being blank causes the whole row of data to disappear.

Is there any way to address this?

[v-yueyunzh-msft · Accepted solution]

HI ,  @SevsBo 

According to your description, when you add a measure on the visual , your Visual will have a few less lines unless you turn on "SHOW Items with No Data".

This is normal, because your measure returns BLANK () in a specific column, then this line will not be displayed in Table Visual.

Like this table:

If we create a measure like this:

Measure = IF(MAX('Table'[Column1])="a" , BLANK() , SUM('Table'[Column2]))

When we put the measure on the visual , If the values of the dimension field in your table visual back are returned to Blank (), then your line will not be displayed, unless you open the "Show item with no data" unless you open the dimension field. For the dimension field, it refers to the fields that have not been aggrated.

But when we modify the measure to aviod return the balnk value, it will show the all dimension data now:

Measure = IF(MAX('Table'[Column1])="a" , "my is not blank" , SUM('Table'[Column2]))

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly