Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Vishruti
Regular Visitor

Desktop and Paginated Report Not Showing Blank Years

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?

1 ACCEPTED SOLUTION
v-jtian-msft
Community Support
Community Support

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"

vjtianmsft_0-1715159054593.png

1.create index column (table_test)

vjtianmsft_1-1715159112866.png

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

vjtianmsft_2-1715159173640.png

 

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.

vjtianmsft_3-1715159190845.png

 

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

vjtianmsft_4-1715159203164.png

 

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

vjtianmsft_5-1715159305048.png

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

vjtianmsft_6-1715159338133.png

Result like this:

vjtianmsft_7-1715159372219.png

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.



View solution in original post

3 REPLIES 3
v-jtian-msft
Community Support
Community Support

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"

vjtianmsft_0-1715159054593.png

1.create index column (table_test)

vjtianmsft_1-1715159112866.png

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

vjtianmsft_2-1715159173640.png

 

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.

vjtianmsft_3-1715159190845.png

 

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

vjtianmsft_4-1715159203164.png

 

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

vjtianmsft_5-1715159305048.png

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

vjtianmsft_6-1715159338133.png

Result like this:

vjtianmsft_7-1715159372219.png

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
Helper IV
Helper IV

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.