Forum Discussion

JB_AT's avatar
JB_AT
Icon for Helper III rankHelper III
1 year ago
Solved

Return 0 when blank

Hello


I would like to return 0 instead of blank().

I tried following the instructions here https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax/ and other variations, but the results seem to ignore any filters I have on the report and populates in data fields where it shouldn't.

I have a matrix visual with columns from the Date & Department dimensions. Which have a one to many relationship with the FACT table. The Fact Table collects lets say Deadline Data and if there is no date for a Deadline then there 

When I add any of the measures it shows 0 in all Dates  where the result is BLANK(), Ideally I only want to show 0 on the dates that appear only in the FACT table and not all dates from the Date table

Entries = 
IF (
    CALCULATE( 
        COUNT('FACTTable'[ID]),
        'FACTTable'[Valid Entry] = 1 ) = BLANK(),
        0, 
        CALCULATE( 
        COUNT('FACTTable'[ID]),
        'FACTTable'[Valid Entry] = 1 )
)

 Thanks in advance

Need Help
  • BeaBF's avatar
    BeaBF
    1 year ago

    JB_AT Hi! Try with:

     

    Entries =
    VAR ValidEntries =
    CALCULATE(
    COUNT('FACTTable'[ID]),
    'FACTTable'[Valid Entry] = 1
    )
    RETURN
    IF(
    ISBLANK(ValidEntries),
    0,
    ValidEntries
    )

     

    BBF

  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I tried to create a sample pbix file like below.

    Please check [Entry measure fix:] measure in the below picture and the attached pbix file.

     

     

     

     

     

    Entry measure fix: = 
VAR _periodstart =
    CALCULATE ( MIN ( 'fact'[date] ), REMOVEFILTERS ( 'calendar' ) )
VAR _periodend =
    CALCULATE ( MAX ( 'fact'[date] ), REMOVEFILTERS ( 'calendar' ) )
VAR _result =
    CALCULATE (
        COUNTROWS ( VALUES ( 'fact'[department_id] ) ),
        'fact'[valid_entry] = 1
    )
RETURN
    IF (
        MAX ( 'calendar'[Date] ) >= _periodstart
            && MIN ( 'calendar'[Date] ) <= _periodend,
        _result + 0
    )

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    I tried to create a sample pbix file like below.

    Please check [Entry measure fix:] measure in the below picture and the attached pbix file.

     

     

     

     

     

    Entry measure fix: = 
VAR _periodstart =
    CALCULATE ( MIN ( 'fact'[date] ), REMOVEFILTERS ( 'calendar' ) )
VAR _periodend =
    CALCULATE ( MAX ( 'fact'[date] ), REMOVEFILTERS ( 'calendar' ) )
VAR _result =
    CALCULATE (
        COUNTROWS ( VALUES ( 'fact'[department_id] ) ),
        'fact'[valid_entry] = 1
    )
RETURN
    IF (
        MAX ( 'calendar'[Date] ) >= _periodstart
            && MIN ( 'calendar'[Date] ) <= _periodend,
        _result + 0
    )

     

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    1 year ago

    JB_AT Hi! Try with:

     

    Entries =
    VAR ValidEntries =
    CALCULATE(
    COUNT('FACTTable'[ID]),
    'FACTTable'[Valid Entry] = 1
    )
    RETURN
    IF(
    ISBLANK(ValidEntries),
    0,
    ValidEntries
    )

     

    BBF