How to replace blank counts on tile with zero

[kedarp]

Hi All,

We are trying to develop a report where 2source tables are used from SQL server direct query.

Design of report is there are multiple filters and based on selection, distinct count of both the tables shown on 2 different tiles.

Relationship is many to many and filter is bidirectional.

Problem is when there is no match in data for particaular combination of dropdowns, it will show blank. We need to show blank values as 0 on tiles.

Any example to acieve this will be of great help.

-----------------

Please consider following sample scenario : 

We have 2 tables 

1.Colleges : [CollegeID,CollegeName,Faculty,Campus,Reputation,State,City]

2.Participants : [ParticipantID,ParticipantName,CollegeName,PassingYear,HomeState,HomeCity]

• Report design is on left dropdowns from college table, on right dropdowns from participants.(cross filters)
• Distinct count of colleges and participants shown in center.These gets filterted by dropdown selection.
• All the dropdowns are configured with "Show Select all option" ON and "Single Select" as OFF

When Home state is selected as Goa, Colleges is blank as there is no matching data in colleges table. We need to replace this with more meaningful value like 0.

-------------

Attached : 

Tables, reports screenshotColleges tableParticipant tableReport

[Anonymous]

Hello @kedarp,

if you go in to your data tables, there should be an option at the top to select blank cells as zero and not blank. 

hope it helps,

Collin

[kedarp · Accepted solution]

Hi Collin,

Thanks for your reply.

However I am able to solve my problem with dax expression using islank, if functions.