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 th...
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
    )

     

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
    )