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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
avulasandeep
Helper III
Helper III

If we select date in slicer display sales of the products that have null display as zero

Hi every one,

If we select date in a slicer display the products that does have sales show the null sales as zero.

Here is link for previous post please reply it's urgent .
https://community.powerbi.com/t5/Desktop/if-isfiltered-then-display-sales-for-null-values-also/m-p/7...

Thanks
Sandeep

1 ACCEPTED SOLUTION

Hey,

 

I understand now.

 

You should consider to create a star schema, a one table solution has always its shortcomings

 

First I created a table Dimension Product using this DAX statement:

dimension product = DISTINCT('Sheet1'[Products])

Then I created a relationship between your data table (the fact table) and the new dimension product table:

image.png

I created a measure (it is more convenient) to reference the measure then to always write the complete formula:

Total Value = 
SUM('Sheet1'[Value])

Then I write the final measure:

Value showing zeros = 
var theValue = [Total Value]
return
IF(ISBLANK(theValue) , 0 , theValue)

This allows to create the following table:

image.png

Please be aware that the Products are coming from the Table "Dimension Product" and not from the data table. This is a best practice as well as data modeling using a star schema.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

8 REPLIES 8
TomMartens
Super User
Super User

Hey,

I would create two measures the first only aggregates just the sales like so:
Total Sales = SUM('<name of the table>'[name of the column])

And a second measure that checks if the measure is blank like so:
Total Sales with zero =
var maybeblank = [Total Sales]
return
IF(ISBLANK(maybeblank) , 0 , maybeblank)

Hopefully this is what you are looking for.

Regards,
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @Anonymous ,

Here I need to select month slicer ..if I select month from the slicer ....then the products of the sales having null has to display as zero along with products of the sales not having nulls or zero for that selected month.
Please refer my question or post from above

Thanks
Sandeep

Hey,

 

please prepare a pbix file that contains sample data and reflects your data model, upload the pbix to onedrive or sharepoint and share the link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @Anonymous ,
Can you please check I have updated the link for downloading pbix file.

Thanks
Sandeep

Hey,

 

I understand now.

 

You should consider to create a star schema, a one table solution has always its shortcomings

 

First I created a table Dimension Product using this DAX statement:

dimension product = DISTINCT('Sheet1'[Products])

Then I created a relationship between your data table (the fact table) and the new dimension product table:

image.png

I created a measure (it is more convenient) to reference the measure then to always write the complete formula:

Total Value = 
SUM('Sheet1'[Value])

Then I write the final measure:

Value showing zeros = 
var theValue = [Total Value]
return
IF(ISBLANK(theValue) , 0 , theValue)

This allows to create the following table:

image.png

Please be aware that the Products are coming from the Table "Dimension Product" and not from the data table. This is a best practice as well as data modeling using a star schema.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Superrrrr.....thanks

It's working fine. Thanks once again


Thanks
Sandeep

Hey,

 

the link you provided requires a login, I'm not going to login to download your sample data. Maybe you can provide another link or update the settings of that link.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.