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
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
Solved! Go to 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 :
@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 :
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
102 | |
94 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |