Display 0 value instead of BLANK values and RANK the total count and restrict only top 5 rows

[RKothaneth]

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 = 

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

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

[RKothaneth · Accepted solution]

@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]

@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