The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
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.
@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])
)
Proud to be a Super User! | |
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
261 | |
120 | |
113 | |
83 | |
71 |