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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nkasdali
Employee
Employee

Show items with no data Paginated Report

Hi All, 

Any tips to force my matrix to display all my dimension even if my measure is null ?

 

regards

5 REPLIES 5
jbugmann
New Member

Hi All,

 

I've got the solution.

 

The key to this problem is learning how to add the "ADDMISSINGITEMS" dax function to your dataset query on Paginated Reports builder.

 

Steps:

 

1. Open your paginated report, connect it to your database source and create a new dataset by selecting your columns and parameters, as usual.

 

2. After you have done that and your dataset query has been generated, you need to edit it by manually adding the "ADDMISSINGITEMS()" dax function to your "SUMMARIZECOLUMNS" function that has been generated automatically. You can find many videos and explanations on the internet on how to use the ADDMISSINGITEMS function.

 

3. (short explanation about the function) Add to the ADDMISSINGITEMS function the columns you would like to retrieve items with no data as a first argument, then paste your SUMMIRIZECOLUMNS function as a second argument, and finally add all the columns you selected in your initial query as a third argument (all those which are not measures). And magic, the problem has been solved.

 

Lastly, in case it helps a bit further, this is a way you can look at your new query in very rough terms:

 

EVALUATE ADDMISSINGITEMS(columns_to_show_null_values, SUMMIRIZECOLUMNS(....), columns_selected_in_your_query)

Best wishes.

Yanant1020
Advocate I
Advocate I

Hello, is there any more information available on this topic? We are running into the same issue.

Fowmy
Super User
Super User

@nkasdali 

You can add +0 to your measure though it has some performance impact.

How about if you select "Show Item with No Data" ?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

hi @Fowmy unfortunalty "Show Item with no data" is in the Power BI Desktop, and i'm using Power BI Report Builder.

 

i'll try your +0 tips 😉

 

thanks

@nkasdali 


I didn't notice that in the title. Let me get back to you if I can figure out a way.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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