Replacing Blanks with Zeros in a Matrix - Date filter no data

[romovaro]

HI

I have seen different solutions to this issue in the forum but it seems none is working for me.

I ahve a table showing the number of projects going Live x month and the Productivity owner name

Num of projects = All projects with Same customer, same country and same Go Live date = 1 project

The formula below and using the Index column checks previous row for this requierements and shows 0 until condition is met, then 1.

NumProjects = IF (

    OR ( OR (

        'PV Cel File'[CID]<>

    CALCULATE (

        MAX ('PV Cel File'[CID]),

        FILTER (

            'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

    ),

    'PV Cel File'[Country Long Name]<>

    CALCULATE (

        MAX ('PV Cel File'[Country Long Name]),

        FILTER (

            'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

    )

    ),

        'PV Cel File'[GLD]<>

        CALCULATE(

            MAX('PV Cel File'[GLD]),

            FILTER(

                'PV Cel File',

            'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1)

        )

    )

    ,1

    ,0

    )

I can have different projects rows but if same country/Same Date and same customer...al rows will show 0 except one row that will show 1.

I would like to show 0 when no projects are scheduled in that month instead of blank so my Target and diff formula works.

Any tip?

thanks

[Uzi2019]

Hi @romovaro 

You can simply add 0 to your measure or create one measure with +0 at the end.

measure= calculate----------

return result +0

I hope this might solve your issue!

[romovaro]

HI @Uzi2019 

I also treid this one but nothing:

Measure = calculate(SUM('PV Cel File'[NumProjects]))+0

I keep getting blanks

My Measure = Old formuola for Projects

[bhanu_gautam]

@romovaro Yoyu can use the IF function to check for blanks and replace them with zeros

DAX
NumProjects =
IF (
OR (
OR (
'PV Cel File'[CID] <>
CALCULATE (
MAX ('PV Cel File'[CID]),
FILTER (
'PV Cel File',
'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1
)
),
'PV Cel File'[Country Long Name] <>
CALCULATE (
MAX ('PV Cel File'[Country Long Name]),
FILTER (
'PV Cel File',
'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1
)
)
),
'PV Cel File'[GLD] <>
CALCULATE (
MAX ('PV Cel File'[GLD]),
FILTER (
'PV Cel File',
'PV Cel File'[Índice] = EARLIER ('PV Cel File'[Índice])-1
)
)
),
1,
0
)

you can wrap your NumProjects measure in another IF statement to check for blanks and replace them with zeros:

DAX
NumProjectsWithZero =
IF (
ISBLANK([NumProjects]),
0,
[NumProjects]
)

[romovaro]

HI @bhanu_gautam 

Thanks for your quick response.

Unfortunately I get the same results and blanks.

SumProjectsWithZero =

IF (

ISBLANK(SUM('PV Cel File'[NumProjects])),0,SUM('PV Cel File'[NumProjects]))

the issue in my case is that blanks are "Non existent". I filter by date and if blank, that means that the table has no data for this month/Product manager...

Eerything comes from one table except the target that is located in another table.

In Excel would be similar to this: