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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
CCK
Frequent Visitor

Include and have blank cells in the report in those instances where values are (null) values

Hi,

My paginated report does currently not include the following (null) values observed in the query designer in the report. I want the report to show the blank cells for the groups with (null) values in report. I have tried several solutions, but none of them seem to provide me with the intended results. In addition, I am fairly new to creating paginated reports, so please bear with me. 

 

Thank you in advance,

Anmærkning 2022-09-23 173027.png

3 REPLIES 3
CCK
Frequent Visitor

CCK
Frequent Visitor

Hi Henry,

Thank you for guiding me in right direction. I do, indeed, believe that the solution you are referring to is highly relevant for my request. However, I think that I am using an incorrect DAX syntax when I try to add to the existing Query underlying the data a filter(value(…) that should include both values and (null) values in the report.

 

I was therefore hoping that a stylized data example mimicking the actual data (the actual data is confidential) would make it easier for you to further elaborate on how to correctly incorporate the filter(value(…) formulas into the query.

So, the following DAX formula in the query designer:

 

EVALUATE SUMMARIZECOLUMNS('CALENDAR'[DATEFULL], FILTER(VALUES('RESULTS'[TEAM]), ('RESULTS'[TEAM] = "TEAM A") || ('RESULTS'[TEAM] = "TEAM B"), FILTER(VALUES('RESULTS'[AGE]), ('RESULTS'[AGE] = "CHILD") ||('RESULTS'[AGE] = "TEEN") || ('RESULTS'[AGE] = "ADULT"), "TODAY", [TODAY], "YESTERDAY", [YESTERDAY], "NUMBER", [NUMBER]),

 

yields the following data,

 

DATEFULLTEAMAGETODAYYESTERDAYNumber
26-09-2022TEAM AADULT2981
26-09-2022TEAM BADULT6871
26-09-2022TEAM ACHILD5921
26-09-2022TEAM BCHILD3751
26-09-2022TEAM ATEEN(null)(null)1
26-09-2022TEAM BTEEN(null)(null)1

 

The current report does not include the (null) values for “TEEN” in TEAM A and TEAM B. I want TEEN in TEAM A and TEAM B to be represented in the report, but with blank/empty cells. 

Than you in advance,

v-henryk-mstf
Community Support
Community Support

Hi @CCK ,

 

According to your description, you can refer to the following similar solution to display the value as empty by creating a dax.

Solved: Paginated Report Multivalue Parameter and DAX to i... - Microsoft Power BI Community


If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.


Best Regards,
Henry


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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