Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RKothaneth
New Member

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 

Accounts2022 Q32022 Q42023 Q12023 Q22023 Q3Total
Account 23123 9
Account 452 2 9
Account 9441  9
Account 61331 8
Account 5 242 8
Account 71 43 8
Account 12 23 7
Account 311 4 6
Account 1232 1 6
Account 10 311 5
Account 1121 115
Account 83 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 = 

IF(ISBLANK(COUNT('Account'[Number])), 0, COUNT('Account'[Number])).  But I show only certain records as 0 and still shows BLANK records for certain accounts. Here in my table there are no actual null values for the columns, the data doesnt exist and I believe that si why it is coming as BLANK records.
 
Now to get top 5 records, I used RANKX by creating a calculated column, 
Named Rank = RANKX( ALLSELECTED('Account'), 'Account'[Account])  and then created a measure 

_Ranking =
RANKX(
    ALLSELECTED('Account'[Account]),
    CALCULATE(COUNT('Account'[Number]))+ INT(CALCULATE(MAX('Account'[Named Rank])))/100000,
    ,DESC,Dense
)
 
When I applied this measure as a filter and selects "Is less than or equal to" 5, my 2023 Q3 columns gets removed as no records falls within this filter criteria and it looks like below. Where I would still want to see 2023 Q3 column with values as 0 for the top 5 records

 

Accounts2022 Q32022 Q42023 Q12023 Q2Total
Account 231239
Account 452 29
Account 9441 9
Account 613318
Account 5 2428
Account 71 438

 

Could you please tell me, if I missing anything here?

 

Thanks,
Rohith

1 ACCEPTED SOLUTION
RKothaneth
New Member

@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 measure 
Countvalue =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.

View solution in original post

2 REPLIES 2
RKothaneth
New Member

@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 measure 
Countvalue =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.
amitchandak
Super User
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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.