Show value for measure with a result of 0 (in a matrix)

[Arkhos94]

I have a measure that calculated the % of a task done from 2017 to now :

%PerformedYTD = calculate([Performed],DATESbetween(TaskCalendar[Date],DATE(2017,1,1),max(TaskCalendar[Date])))/[ToBePerformed]

Performed = count(TaskList[Count task Performed])

ToBePerformed = count(TaskList[Count task ToBePerformed])

The calculated value "%PerformedYTD" works fine and I show it on a matrix with "year-month" as column and the task ID as line

Problem : when a task has a %performed of 0% to date (Performed = 0, ToBePerformed has a value >0), then no line is shown for this task ID

Instead, I would like to show an empty line

[Arkhos94]

Hello @v-cazheng-msft I'm looking for something in between the two screencapture above : 

I want every task displayed like in the second one

But I want the cells with a null value to be as discreet as possible

If I make the cells with a null value into a blank, then the task with a null value up to date dissapear 

So I need to replace null value with some character and make it as small and invisible as possible

- is good but if you have an idea of a less visible character (or a way to make - even less visible), I would take it

[v-cazheng-msft · Accepted solution]

Hi, @Arkhos94 

You can try this measure. If it doesn't work, I will try to find other solutions.

%PerformedYTDv02 =

VAR res =

    CALCULATE (

        [Performed],

        DATESBETWEEN (

            TaskCalendar[Date],

            DATE ( 2017, 1, 1 ),

            MAX ( TaskCalendar[Date] )

        )

    )

RETURN

    IF ( ISBLANK ( res ), "", res / [ToBePerformed] )

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Arkhos94]

Hello @v-cazheng-msft : I tried replacing "-" by a blank (both with " " like you propose or blank()) but both get me back to my original situation where task with 0% realised are not shown

From what I understand, I need something for 0% task, but not a blank,  if I want them to have a line on my table.

The question is what "something" would be the most discreet (the smallest symbol with maybe some conditional formating).

If you've got an idea, it would be very welcome

[v-cazheng-msft]

Hi, @Arkhos94 

Sorry, I am not sure about your needs. Can you display the results you want in a table format?

Best Regards

Caiyun Zheng

[v-cazheng-msft]

Hi, @Arkhos94 

Do you want to replace ‘-‘ with blank? If so, you can try to make some changes to the dax formula.

%PerformedYTDv02 =

IF (

    AND (

        CALCULATE (

            [Performed],

            DATESBETWEEN (

                TaskCalendar[Date],

                DATE ( 2017, 1, 1 ),

                MAX ( TaskCalendar[Date] )

            )

        ) + 0 = 0,

        [ToBePerformed] > 0

    ),

    "",

    CALCULATE (

        [Performed],

        DATESBETWEEN (

            TaskCalendar[Date],

            DATE ( 2017, 1, 1 ),

            MAX ( TaskCalendar[Date] )

        )

    ) / [ToBePerformed]

)

Best Regards,

Caiyun Zheng

[Arkhos94]

@amitchandak : thanks for your help

I tried the first option and it does not change anything

The second one work (every task is shown, even the one with %PerformedYTD=0 to date) but create a new problem

Right now, as long as the KPI is 0 no value is shown and the % only start to appear when the value is above 0. As shown in this picture :

 

If I use your second formula, every empty space is replaced by 0%

It's nice because every task is shown (even the one with a 0% result as of today) but it's much less readable

I took your "+0" idea and played with it a little bit to change my formula : 

%PerformedYTDv02 = if(and(calculate([Performed],DATESbetween(TaskCalendar[Date],DATE(2017,1,1),max(TaskCalendar[Date])))+0=0,[ToBePerformed]>0),"-",calculate([Performed],DATESbetween(TaskCalendar[Date],DATE(2017,1,1),max(TaskCalendar[Date])))/[ToBePerformed])

So if the task has 0 realised but has more than 0 to be performed a - value is shown

If the task has 0 to be performed => the previous formulas is used and nothing is shown => great

If the task has more than 0 realised and has more than 0 to be performed  => the previous formulas is used and nothing is shown => great

Do you have any idea on what I could put instead of - that would be less visible ? (a simple space of blank() do not work, I tried)

[amitchandak]

@Arkhos94 , Not very clear.

Try like

%PerformedYTD = divide(calculate([Performed],DATESbetween(CampagneCalendar[Date],DATE(2017,1,1),max(CampagneCalendar[Date]))),[ToBePerformed],0)

or

%PerformedYTD = divide(calculate([Performed],DATESbetween(CampagneCalendar[Date],DATE(2017,1,1),max(CampagneCalendar[Date]))),[ToBePerformed],0)+0