How to Keep All Values of a Field When Using Measure on a Table Visual

[rashidanwar]

I have a dataset in Power BI desktop. It contains 3 columns of data.
title, approval_date, and amount. I have also a measure named approved_amount. The measure is summing the amount column where approval date column in not blank.

On the report page, I have a table visual containing title, approval_date fileds and approved_amount measure.

I want to show all the titles even when approval date is blank. In that case approved_amount measure will show as blank.

But the problem is that when I add the approved_amount measureto the table visual it automatically filters out the title where approval date is blank. In other words table shows only those records where approval date is not blank.

Any help in this regard would be highly appreciated.

Thanks!
Rashid Anwar 

[rashidanwar]

Hi @bhanu_gautam, I did a work around as explained below. If anyone could suggest a better solution to avoid additions of calculated column and conditional formatting, that would be great.

My solution:

I created a calculated column named "approved_amount_new." This column uses an IF statement to assign 0 to rows where "approval date" is blank and retains the original amount otherwise. By using "approved_amount_new" in the table visual, I ensured all titles were displayed. I then applied conditional formatting to make zeros in the "Amount" column transparent.

[v-zhengdxu-msft · Accepted solution]

Hi @rashidanwar 

Glad to hear you solved the problem yourself! Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

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

[bhanu_gautam]

@rashidanwar , You can create a measure using ALL function which keeps all the values of title

approved_amount =
CALCULATE(
SUM('YourTableName'[amount]),
ALL('YourTableName'[approval_date])
)

[rashidanwar]

Thank you @bhanu_gautam for your response.
ALL removes all the filter contexts so in our case we have outer filters as well coming from the Slicers. ALLSELECTED could do the job. I tried it but it doesn't work in my scenario. I am experiencing the same behaviour as  before using the ALLSELECTED.