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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
prab
Microsoft Employee
Microsoft Employee

Measure to return 0(zero) if distinct count is blank

Need some help from the community. 

 

I am trying to get the count of programs for each month (date as lastdayofthemonth) with target as above. However if for any month, there are no program with target as 'Above' then the measure should return 0. 

 

Measure = CALCULATE(DISTINCTCOUNT('Table'[Program]),'Table'[Target (Below/Above)]="Above")
In the "Month wise Above" visual want see "Friday January 31" as 0,  
 

image.png

Sample data

 

MonthYearProgramLocationProjectTarget (Below/Above)LastdayofMonth
1/1/2020ABCHydp1below1/31/2020
1/1/2020XYZHydp2below1/31/2020
1/1/2020TEST1Hydp1below1/31/2020
1/1/2020ABCHydp2below1/31/2020
1/1/2020XYZHydp2below1/31/2020
1/1/2020cb1blrp2within1/31/2020
1/1/2020dumblrp2within1/31/2020
1/1/2020vixblrp2within1/31/2020
1/1/2020bullblrp2within1/31/2020
12/1/2019ABCHydp1below12/31/2019
12/1/2019XYZHydp2below12/31/2019
12/1/2019TEST1Hydp1above12/31/2019
12/1/2019ABCHydp2below12/31/2019
12/1/2019XYZHydp2below12/31/2019
12/1/2019cb1blrp2within12/31/2019
12/1/2019dumblrp2below12/31/2019
12/1/2019vixblrp2within12/31/2019
12/1/2019bullblrp2within12/31/2019
11/1/2019ABCHydp1below11/30/2019
11/1/2019XYZHydp2below11/30/2019
11/1/2019TEST1Hydp1within11/30/2019
11/1/2019ABCHydp2above11/30/2019
11/1/2019XYZHydp2below11/30/2019
11/1/2019cb1blrp2within11/30/2019
11/1/2019dumblrp2below11/30/2019
11/1/2019vixblrp2within11/30/2019
11/1/2019bullblrp2within11/30/2019
1 ACCEPTED SOLUTION

Your source data doesn't have an "Above" record for Jan 31, 2020.  The measure will return "0" with no issue, but your data table doesn't have a row with "Above" on it to provide any filter context for the measure to work.

 

You may want to create a Dimension table with Above, Below, and Within, and use that table to populate your visuals, creating a One-To-Many to your Fact table, then everything will have an Above/Below/Within, and the measure would then work.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

9 REPLIES 9
farid_0709
Frequent Visitor

@amitchandak why you have used +0 at the end of Distinctcount?

prab
Microsoft Employee
Microsoft Employee

Thank You @edhans for quick solution. 

 

However the measure works unitl the Target is not added to the table visual.

Below in the highlighted Visual count for "Above" is missng for January. 

Is there a way to fix this.

 

Thank Again. 

 

image.png

Your source data doesn't have an "Above" record for Jan 31, 2020.  The measure will return "0" with no issue, but your data table doesn't have a row with "Above" on it to provide any filter context for the measure to work.

 

You may want to create a Dimension table with Above, Below, and Within, and use that table to populate your visuals, creating a One-To-Many to your Fact table, then everything will have an Above/Below/Within, and the measure would then work.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
prab
Microsoft Employee
Microsoft Employee

Thank You @edhans it works..

 

 

Great @prab. Glad it works for you.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

try

Measure = CALCULATE(DISTINCTCOUNT('Table'[Program]),'Table'[Target (Below/Above)]="Above")+0

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

What does the + zero do at the end?

It converts a blank to a zero. See Handling BLANK in DAX - SQLBI - the example there is BLANK()+4 = 4, but BLANK()+0 equals 0.
Another way to do it is to use the COALESCE() function. 

COALESCE([Some Measure or Expression], 0) would return the first non-blank value. If the measure is blank, it returns zero.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

If your measure is correctly returning what you want, and the issue is if it returns blank you want it to return 0 instead, use this:

 

Measure =
VAR ProgramCount =
    CALCULATE(
        DISTINCTCOUNT( 'Table'[Program] ),
        'Table'[Target (Below/Above)] = "Above"
    )
RETURN
    IF(
        ProgramCount = BLANK(),
        0,
        ProgramCount
    )

 

It simply says if ProgramCount (which is your measure in the VAR statement) is blank, return 0, otherwise the value of ProgramCount.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
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!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.