Desktop and Paginated Report Not Showing Blank Years

[Vishruti]

Hello,

I am generating Paginated Excel Reports through Desktop Export to Excel DAX.

I have a Year slicer with "between" style, which means a user can select From and To year in the slicer. I also have some data points where the Year is blank (No date in date column). 

User wants to see all records (even the ones that have no date in date columns and thats why no Year) and user also wants to be able to select the From and To year (therefore, I need to use the "between" slicer for Year.

How can I achieve this in Desktop report? And in export excel through Paginated reports as well?

[v-jtian-msft · Accepted solution]

Hi,@Vishruti.  Hello, @jay_patel  ,thank you very much for some effective help on these issues!
And I would like to share some of my suggestions.

According to your description, you want to see all records (even the ones that have no date in date columns and thats why no Year) and user also wants to be able to select the From and To year.
You could try the solution below.
Here is the test data I created.
The Table Named''table_test"

1.create index column (table_test)

2.Create a new table to hold all the years of your data separately (without null) named Table

3.Create a calculated column named 'Year' in the 'table_test' table to get all the non-null years of the date columns in the data table.

4.Create a measure to  distinguish the data(which needs to be treated as a filter)

Below is the Dax code:

 M_2 = 
IF(SELECTEDVALUE(Table_test[Year])IN
 ALLSELECTED('Table'[year])|| SELECTEDVALUE('Table_test'[Year])=BLANK(),
 1,0
 ) 

4.Create a slicer using the 'Year' field from the publicly created table

Add the M_2 metric as a filter condition to the data table table_test. This will allow you to use the between and also display the data with the 'Date' column as null

Result like this:

Finish!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jay_patel]

If possible, just add a new column... if dates are blank then 0 else dates, and try to drag that in Query Designer not in the preview of paginated report.

[Vishruti]

Hi, 

I have tried this solution and it is a good option. However, the problem is that it works only when the between slicer is at its default setting (from min to max of the year values). But if you select any other start or end year then the Dektop report itself does not show the rows with balnk Year.