Show blanks in matrix as 0 while also keeping filters

[BenJCross95]

All,

I am creating a report using the usage metrics data from a selected workspace.  I am creating a matrix to show the number of page views and the days since the page was last viewed as a way to indicate that i can hide unused/barely used pages in a report to de-clutter the environment in the service.

I have turned on show items with no data for the SectionName but now i would like to replace the blank in PageViews with a 0 and the blank in Days Since Last Open with 31 if PageViews is blank. The usage metrics data is on a rolling month window hence 31 days. The table i currently have is below.

Doing the usual trick of PageViews + 0 and DaysSinceLastOpen + 31 if the values are blank removes the filter on the ReportName and returns every page from every report in the workspace. Is there a way to add the 0 and 31 but still keep the current filter context on the report name.

[arichard19]

Have you tried creating calculated columns in the original tables yet?

For Example:

Column[Views] = IF( ISBLANK(Table1[PageViews]), 0, Table1[PageViews])

Based on the column you can then sum as following:

Measures[View] = CALCULATE(SUMX(Table1, Table1[Views]))

[BenJCross95 · Accepted solution]

Thanks for the quick response!

Your response helped me to get to the answer, it wouldn't work originally - i blame direct query. I created the following measures (with the measure [Page views] being pre-included in the model;

Report Page Views =

VAR CreatedTable =

ADDCOLUMNS(

'Report pages',

"@Views",

[Page views]

)

VAR Result =

sumx(CreatedTable, [@Views])

RETURN

Result

Then i created;

Days Since Last Open =

IF(

ISBLANK([Report Page Views]),

blank(),

IF(

[Report Page Views] = 0,

31,

int(TODAY() - max('Report page views'[Date]))

)

)

Which resulted in the correct table;

Thanks for the help!!