Items with no data, conditional formatting and drill through

[Theiren]

I have a matrix visualisation showing some numbers divided by some dimensions. From the visualisation it is possible to drill through to a detail page. Below a scetch of my viz with nonsense data, yellow indicating the number I am about to drill through in my next step:

The detail page includes a table visualisation including a column "COMMENTS" calculating comments related to that particular row. Sometimes the result is blank, which hides the whole row if the "Show items with no data" is not chosen. However, when there are no comments, the conditional formatting (link) disappears from the "COMPANY" column.



This can be avoided by replacing the blanks with zeros. The problem is, that when doing so, the drill through stops working, and all the rows become visible.



I have tried with:

1) IF(ISBLANK(SUM(comment[count_of_comment])), 0, SUM(comment[count_of_comment]))
2) SUM(comment[count_of_comment]) + 0
3) COALESCE(SUM(comment[count_of_comment]), 0)

All of the above result to the same issue.

Any ideas how to get the drill through working again (with conditional formatting working as well)? Thanks!

[Theiren]

Hi @Anonymous 

Thanks a lot for your reply. Indeed, I noticed already earlier, that my issue must be related to table relations, because if I took values from one table only, this didn't happen.

I modified your pbix a bit and added three example tables which describe more my real world case. The measure I created based on your suggestion was:

commentCount2 = 
VAR _List = CALCULATETABLE(VALUES(Theiren_Orders[Size]), ALLSELECTED(Theiren_Orders[Size]))
RETURN
IF(MAX(Theiren_Orders[Size]) IN _List, CALCULATE(SUM(Theiren_Comments[count_of_comment]) + 0))

And this did the trick (the only diff compared to yours was that I have the size information only available in the "Orders" table, but it worked like this anyway)! I would like to attach the modified pbix here to help others, but I can't see any attachment options.

[Anonymous · Accepted solution]

Hi @Theiren ,

Your issue should be caused by relationship between tables. I think there should be a relationship based on size columns between the data table (in matrix) and a company table (in table visual).

I create a sample to have a test.

Data table:

Company table:

Comment table:

I think you can try this code to achieve your goal.

Measure = 
VAR _List = CALCULATETABLE(VALUES('Table'[Size]),ALLSELECTED('Table'[Size]))
RETURN
IF(MAX(Company[SIZE]) IN _List,CALCULATE(SUM(Commnet[count_of_comment])+0))

Result is as below.

Best Regards,
Rico Zhou

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