Forum Discussion
Display 0 value instead of BLANK values and RANK the total count and restrict only top 5 rows
Hello Power Bi Desktop team,
I have a requirement where I would want to display 0 value in place of BLANK records in my matrix table and finally restrict the data in the table to show only Top 5 rows based on the Total counts.
Here is a sample data
| Accounts | 2022 Q3 | 2022 Q4 | 2023 Q1 | 2023 Q2 | 2023 Q3 | Total |
| Account 2 | 3 | 1 | 2 | 3 | 9 | |
| Account 4 | 5 | 2 | 2 | 9 | ||
| Account 9 | 4 | 4 | 1 | 9 | ||
| Account 6 | 1 | 3 | 3 | 1 | 8 | |
| Account 5 | 2 | 4 | 2 | 8 | ||
| Account 7 | 1 | 4 | 3 | 8 | ||
| Account 1 | 2 | 2 | 3 | 7 | ||
| Account 3 | 1 | 1 | 4 | 6 | ||
| Account 12 | 3 | 2 | 1 | 6 | ||
| Account 10 | 3 | 1 | 1 | 5 | ||
| Account 11 | 2 | 1 | 1 | 1 | 5 | |
| Account 8 | 3 | 1 | 4 |
"Accounts" and "Quarter" info columns are from the same table with name "Account".
To get 0 value for BLANK records when COUNT('Account'[Number]), I tried this measure, Accountcount =
| Accounts | 2022 Q3 | 2022 Q4 | 2023 Q1 | 2023 Q2 | Total |
| Account 2 | 3 | 1 | 2 | 3 | 9 |
| Account 4 | 5 | 2 | 2 | 9 | |
| Account 9 | 4 | 4 | 1 | 9 | |
| Account 6 | 1 | 3 | 3 | 1 | 8 |
| Account 5 | 2 | 4 | 2 | 8 | |
| Account 7 | 1 | 4 | 3 | 8 |
Could you please tell me, if I missing anything here?
Thanks,
Rohith
amitchandak Thank you looking into this.
Since I was able to understand that there are no records for the Accounts for the respective quarters and hence it is coming as BLANK. Initially quarter column data was coming from the same table where I have the account column. Included a calender table and used the quarter info from there and along with that including COUNT('Account'[Number])+0 solved my issue.
First created a new _calender table as
_calender= CALENDAR(MIN('Account'[Opened]), MAX('Account'[Closed]))Added the calculated columns for quarter as :
Year Quarter = YEAR([Date]) & " " & "Q" & QUARTER([Date])Then created the table visual using this Year Quarter column. Which gave the necessary output for the respective quarters. Then created a measureCountvalue =IF(ISBLANK(COUNT('Account'[Number])), 0, COUNT('Account'[Number]))This was all the blanks got replaced with 0.Now kept the same RANK logic per my orignal post and it allowed me to restrict the data via filter for top 5 records.
2 Replies
- amitchandak
Super User
RKothaneth , COUNT('Account'[Number])+0 should the job. Works best when Accounts /date etc come from a dimension/master table
In place of Rankx use new rank or rownumber and apply a visual level filter
Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo
Power BI - New DAX Function: Rownumber- https://www.youtube.com/watch?v=yS9-IQjUDwg&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1
You can break ties easily in new functions
amitchandak Thank you looking into this.
Since I was able to understand that there are no records for the Accounts for the respective quarters and hence it is coming as BLANK. Initially quarter column data was coming from the same table where I have the account column. Included a calender table and used the quarter info from there and along with that including COUNT('Account'[Number])+0 solved my issue.
First created a new _calender table as
_calender= CALENDAR(MIN('Account'[Opened]), MAX('Account'[Closed]))Added the calculated columns for quarter as :
Year Quarter = YEAR([Date]) & " " & "Q" & QUARTER([Date])Then created the table visual using this Year Quarter column. Which gave the necessary output for the respective quarters. Then created a measureCountvalue =IF(ISBLANK(COUNT('Account'[Number])), 0, COUNT('Account'[Number]))This was all the blanks got replaced with 0.Now kept the same RANK logic per my orignal post and it allowed me to restrict the data via filter for top 5 records.
